Help Creating list from unique non-zero values!

juajar

New Member
Joined
Aug 25, 2006
Messages
20
Hi,

I have a table which looks like this:
Libro2
BCDEFGHIJKL
2TestTest1Test2Test3Test4Test5Test6Test7Test8Test9Test10
3Juan10%0%10%0%0%0%0%0%0%0%
4Antonio0%10%0%10%0%10%0%10%0%0%
5Fernando0%0%10%0%10%0%10%10%0%0%
6Andres0%0%0%0%0%10%0%0%0%0%
7Felipe10%0%10%10%10%0%0%0%0%0%
Hoja1


What I want is a macro or formulas in which i can chosse or write a name in a cell and it will bring me the HEADINGS AND RESULTS for the values that are not 0 that correspond to that name. Like this:
Libro2
BCDEF
11Test3Test5Test7Test8
12Fernando10%10%10%10%
Hoja1


Basically this is so i can extract the information cleanly from very large table and crete a graph.

Thanks for Any Help!!
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

Gerald Higgins

Well-known Member
Joined
Mar 26, 2007
Messages
9,115
Why exactly do you want to exclude the tests that haven't been taken ?
It seems to me that it MIGHT be easier to understand the charts that you are going to create, if they all have a consistent series of test names on the X axis.
 

gardnertoo

Well-known Member
Joined
Jul 24, 2007
Messages
938
I've got an answer for you. The result looks like this:
juajar.xls
ABCDEFGHIJK
2TestTest 1Test 2Test 3Test 4Test 5Test 6Test 7Test 8Test 9Test 10
3Juan10%0%10%0%0%0%0%0%0%0%
4Antonio0%10%0%10%0%10%0%10%0%0%
5Fernando0%0%10%0%10%0%10%10%0%0%
6Andres0%0%0%0%0%10%0%0%0%0%
7Felipe10%0%10%10%10%0%0%0%0%0%
8
9
10
11Test 3Test 5Test 7Test 8      
12Fernando10%10%10%10%      
juajar

The solution uses both array formulas (confirmed by hitting CTRL+SHIFT+ENTER) and named ranges.

1. Cell A12: set it up for data validation, allowing values from a list, and select the list of student names.

2. Named ranges
. a. The named range called "thetests" is defined by the formula:
Code:
=OFFSET(juajar!$B$2,0,0,1,COUNTA(juajar!$2:$2)-1)
which selects the names of all the tests identified in row 2. This will work even if you add more tests, as long as there are no gaps.
. b. The named range called "thedata" is defined by the formula:
Code:
=OFFSET(juajar!$B$2,MATCH(juajar!$A$12,juajar!$A$3:$A$7,0),0,1,COUNT(juajar!$3:$3))
This code takes the name in cell A12 and looks for it in cells A3:A7, then chooses the test results on the corresponding row.

3. Formulas
The formulas you see in rows 11 and 12 use these named ranges, so they must be entered correctly. For row 11, select all the cells you need, enter the formula in the formula bar, and confirm it all with CTRL+SHIFT+ENTER. Row 12 is not an array formula; enter the formula in cell B12 and copy across.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,349
Messages
5,635,770
Members
416,879
Latest member
Excel_Newbie4980

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top