Making a unique list from multiple columns

[zendog1960]

I have 6 columns (C2:H11). Each list has 10 rows of text items. For this example let's use the first 15 letters of the alphabet (a THRU o). They are in each list random however any one letter can be in 1 list to all the lists.

What I need is a way to make a unique list in column J of all the letters appearing in all the lists, obviously with no duplicates.

Finally in Column K I would like the total number of times each letter in column J appears in all 6 columns.

I have tried a few different formulas but I cannot get anything to work. Can someone help me out here?

Thanks
Zen

 

[sulakvea]

hi zen, can you please explain what this means?

They are in each list random however any one letter can be in 1 list to all the lists.

Edit: - sorry a little too tired. you meant any given letter can be in any number of lists right?

 

[zendog1960]

hi zen, can you please explain what this means?

They are in each list random however any one letter can be in 1 list to all the lists.

Yes I can. List 1 for example can be any of the first 15 letters of the alphabet with no duplicates. List 2 is the same way. In each of the lists they show 10 of the first 15 letters of the alphabet. However they are not in aplhabetic order. they are in random order such as below:

List 1
B
G
F
A
E
K
L
M
O
H

List 2 (Next Column)
O
M
K
I
G
E
C
A
N

There are some letters the same in each column and some that are not. This is true across all 6 columns (Lists)

Hope that helps

 

[sulakvea]

can you create a helper column?

lets say, your data sits in A1:F10

list all the letters (A to Z) in A15:A40. in B15, type =SUM(IF($A$1:$F$10=A15,1,FALSE)) - entered as array, with ctrl+shift+enter, and copy down

this will give you a count for all letters in your list. i cant figure out how to build a pivot table in 2010 - first time trying to use a PT at home. could have told you how to list all letters if i had 2003, maybe someone else can.

 

[zendog1960]

Funny but way back in 2009 I ask this before so here is a link to that thread for the answer for those of you needing to do this too!

Thanks sulakvea for you help!

Post Link:
http://www.mrexcel.com/forum/showthread.php?t=369909

Zen

 

[MikeWx]

There is an array solution to this question if you use Column I with a non-array formula that converts the text values from six columns of 10 rows each to one column of sixty rows. Copy the I2 formula down through I61. I named the range I2:I61 as LIST to shorten the formula in J2. You can hide Column I if you wish. The array formula in J2 is for the range J2:J61 and the array formula in K2 is for the range K2:K61 in case all 60 text value are unique. The K2 formula uses the IFERROR to blank out the remaining formulas if there are not 60 unique values, so the solution requires Excel 2007. In place of single letters, I used names and words and it seemed to work fine.

<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>I2</TD><TD>=INDIRECT(CHAR(66+ROUNDUP(ROW(A1)/10,0))&(ROW(A1)-(10*(ROUNDUP(ROW(A1)/10,0)-1))+1))</TD></TR><TR><TD>J2</TD><TD>{=IFERROR(INDEX(LIST,SMALL(IF(MATCH(LIST,LIST,0)=ROW(INDIRECT("1:"&ROWS(LIST))),MATCH(LIST,LIST,0),""),ROW(A1:A61))),"")}</TD></TR><TR><TD>K2</TD><TD>{=IF(COUNTIF(LIST,J2:J61)>0,COUNTIF(LIST,J2:J61),"")}</TD></TR></TBODY></TABLE></TD></TR><TR><TD>Formula Array:
Produce enclosing { } by entering
formula with CTRL+SHIFT+ENTER!

</TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4


Mike