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

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

Gerald Higgins

Well-known Member
Joined
Mar 26, 2007
Messages
9,257
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,167,404
Messages
5,853,588
Members
431,602
Latest member
akosikervin

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