## create a list dynamically from a matrix of data

Hi

I have a matrix of data with languages across the top (21 of these) and races down the side.

for each intersection there is a possible value of -5 to +5.

Most cells are null

for any race I want to dynamically generate a shorter list of just the non zero values, in descending order of value, and then display that list into a colum on another spreadsheet.

I already am using named ranges to grab the required "Race" and copy all the values to a row below the source data.

What I want to do is have another step which grabs just the non zero data and presents this using just the appropriate number of colums,

..... Lang1 Lang2 Lang3 Lang4

Race1 5 0 -2 1

Race 2 0 5 -4 0

Race 3 2 -5 5 0

Race 4 0 0 5 -3

Race 5 -1 2 5 5

=MyRace 2 -5 5 0

the contents of the MyRace row is grabbed by using names, with a call in cell A 5 to another cell which picks a race name from a drop-down list and an indirect function in the other cells to grab the contents of "Race 3" which is the selected race in this

case.

I want to generate a list

Languages

Lang3 5

Lang1 2

Lang2 -5

This should be generated automatically and can then be displayed on the front interface of my spreadsheet without interaction.

The values within the cells must be able to be added to.

and 0 or null values omitted.

It should be listed in descending numerical value.

If possible I would prefere to avoid using macros.

I know the other way to do it is to remove my original table and have one table for each race, in the right order, and use names to pick the list I want, but if I cn do it more elegantly I would prefer that.

If the solution also works on version 2003 & XP that would be a bonus.

Ruairi

## Solutions to the Problem create a list dynamically from a matrix of data

**Download SmartPCFixer to Fix It (Free)**

With your languages on row1, from column B to column E.

With your races in column A, from row 2 to row 6.

With your "row below the source data" on row 8, from column B to column E.

With your heading "Languages" in cell A10.

In cell A11, put the following formula:

=IF(ISERROR(INDEX(B$1:E$1,MATCH(LARGE(IF(B$8:E$8<>0,B$8:E$8),ROW()-10),B$8:E$8,0))),"",INDEX(B$1:E$1,MATCH(LARGE(IF(B$8:E$8<>0,B$8:E$8),ROW()-10),B$8:E$8,0)))

Note: This is an array formula that has to be confirmed by CTRL+SHIFT+ENTER rather than just ENTER.

In cell B11, put the following formula:

=IF(A11="","",HLOOKUP(A11,$B$1:$E$8,8,FALSE))

(change the B:E, and the 8 and the 10 in these two formulas if you have a source data with more rows and columns.)

Copy down cells A11:B11 as far as needed.

Note: The formula in cell A11 will only work if the values for all languages are unique per race (except 0 that can be in several column.

If the value for different languages can be the same, more logic will have to be put into the formula in cell A11.

Here is one example that will handle multiple languages with the same values:

=IF(ISERROR(INDEX(B$1:E$1,MATCH(LARGE(IF(B$8:E$8<>0,1000*B$8:E$8+COLUMN(B:E)),ROW()-10),1000*B$8:E$8+COLUMN(B:E),0))),"",INDEX(B$1:E$1,MATCH(LARGE(IF(B$8:E$8<>0,1000*B$8:E$8+COLUMN(B:E)),ROW()-10),1000*B$8:E$8+COLUMN(B:E),0)))

Note: This is an array formula that has to be confirmed by CTRL+SHIFT+ENTER rather than just ENTER.

Hope this helps / Lars-Åke

## Running System Update Readiness Command

On running the System Update readiness Command, it will Detect & Replace Incorrect Registry data with the Correct one. Which may ultimately Solve **create a list dynamically from a matrix of data** in Windows.

- Open CMD as Administrator by Simply Right Clicking on Start button > Command Prompt (Admin).
- Now Type or Copy paste "
**DISM.exe /Online /Cleanup-image /Scanhealth**" and hit Enter, - Now again Type or Copy paste this "
**DISM.exe /Online /Cleanup-image /Restorehealth**" and hit Enter. - Close the Command Prompt Window because we are done.

Try and see if you still get the same error create a list dynamically from a matrix of data in your Windows.

Note: You may be prompted to enter your administrator's password when you try to execute certain system tasks!

## Recommended Method to Fix the Problem: create a list dynamically from a matrix of data:

### How to Fix create a list dynamically from a matrix of data with SmartPCFixer?

1. Download SmartPCFixer. Install it on your computer.

2. After the scan is finished, you can see the errors and problems which need to be repaired.

3. The Repair part is finished, the speed of your computer will be much higher than before and the errors have been fixed. You can also use other functions in this software. Like dll downloading, windows updating and print spooler error repair.

Related: How Can You Update & Download NVidia 6100, 6800 GS/XT Display Driver v.260.89 WHQL,Where to Download NVidia GeForce 8800 GT WHQL-certified driver v.196.21,How to Update & Download NVidia GeForce Go 7400 Display Driver v.260.19.12,[Solved] Download NVidia GeForce GTX 460M Driver v.331.82,Download NVidia Quadro Plex Model IV VGA Driver v.304.43 Certified,Way to Download RealTek RTL8100C(L) Driver v.5.01,Way to Download RealTek RTL8100E Drivers v.694,Way to Update & Download RealTek RTL8101L Auto Installation Program v.6.110 driver,Method to Update & Download RealTek RTL8111G PXE and RPL ROM code v.2.58 driver,How to Update & Download RealTek RTL8411B(N) Driver v.10.003,Best Way to Update & Download ASUS A53SV nVidia Graphics Driver v.8.17.12.6686,Method to Herunterladen ASUS K75VJ Intel Rapid Storage Technology Treiber v.11.6.0.1030,How Can You Update & Download ASUS CG8580 Intel Chipset Driver v.9.3.0.1019,Method to Update & Download ASUS K41VD Intel INF Update Driver v.9.1.1.1015,Way to Update & Download ASUS Pro70T NB Probe v.3.0.0026 driver

Read More: Tech Support: cookie blocked from asda click & collect site,Tech Support: convert PDF file to word,Counting nonblank cells Tech Support,How to Resolve - CSS relating to IE 9 vs IE 11?,copy paste Chart as linked Picture [Solved],Closing Internet Explorer causes error,Compare 2 Tables and Calculate the PROFIT Faster,Computer goes to Windows Boot Manager after Start,Connect to one of my network computer remotely when don't allow exception box Marked,computer locking up randomly

## 没有评论:

## 发表评论