How to Use the AVI Converter: How to Resolve - create a list dynamically from a matrix of data?

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.

How to Use the AVI Converter

2016年9月23日星期五

How to Resolve - create a list dynamically from a matrix of data?

create a list dynamically from a matrix of data

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

Running System Update Readiness Command

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?

没有评论:

发表评论