Transpose multi-column array to a single column on one worksheet to a different worksheet

[Jmoz092]

Hi, I'm having difficulty getting a formula to work that I found here. I want to list all unique entries from a multi-column array on one worksheet to a different worksheet, as a single column of data.

VBA or formula solutions are ok.

This is the worksheet formula that I'm trying to get working:

[COLOR=#000000][FONT=&quot]{=IFERROR(IFERROR[COLOR=#006107]([/COLOR]IFERROR[COLOR=#ab30d6]([/COLOR]INDEX[COLOR=#a54a29]([/COLOR]List1, MATCH[COLOR=#33af4a]([/COLOR]0, COUNTIF[COLOR=#ff9c1b]([/COLOR][COLOR=#0057d6]$A$13:A13[/COLOR], List1[COLOR=#ff9c1b])[/COLOR], 0[COLOR=#33af4a])[/COLOR][COLOR=#a54a29])[/COLOR], INDEX[COLOR=#a54a29]([/COLOR]List2, MATCH[COLOR=#33af4a]([/COLOR]0, COUNTIF[COLOR=#ff9c1b]([/COLOR][COLOR=#0057d6]$A$13:A13[/COLOR], List2[COLOR=#ff9c1b])[/COLOR], 0[COLOR=#33af4a])[/COLOR][COLOR=#a54a29])[/COLOR][COLOR=#ab30d6])[/COLOR], INDEX[COLOR=#ab30d6]([/COLOR]List3, MATCH[COLOR=#a54a29]([/COLOR]0, COUNTIF[COLOR=#33af4a]([/COLOR][COLOR=#0057d6]$A$13:A13[/COLOR], List3[COLOR=#33af4a])[/COLOR], 0[COLOR=#a54a29])[/COLOR][COLOR=#ab30d6])[/COLOR][COLOR=#006107])[/COLOR], "")}[/FONT][/COLOR]

I have 3 more lists that I need to tack onto this equation, but I can't figure out how to do it. The lists are: List4, List5, and List6. Also, How can I tweak it to where it does not show blanks from the Lists?

I don't mind using a VBA solution for this as I could then assign it to a button on each worksheet that this needs to happen on.

Thanks for the help.

 

[Jmoz092]

This formula is working for me right now insofar that it pulls all the data from the named lists, but when I drag the formula down in column A (A14:A38), I get a blank (0) at row 20. Is there something I'm doing wrong? I can't have a blank row or 0 show up in the middle of that column's data. Thanks

[COLOR=#000000][FONT='inherit']=IFERROR(IFERROR[COLOR=#006107]([/COLOR]IFERROR[COLOR=#ab30d6]([/COLOR]IFERROR[COLOR=#a54a29]([/COLOR]IFERROR[COLOR=#33af4a]([/COLOR]IFERROR[COLOR=#ff9c1b]([/COLOR]INDEX[COLOR=#fe4fdd]([/COLOR]List1, MATCH[COLOR=#0057d6]([/COLOR]0, COUNTIF[COLOR=#006107]([/COLOR][COLOR=#0057d6]$A$13:A13[/COLOR], List1[COLOR=#006107])[/COLOR], 0[COLOR=#0057d6])[/COLOR][COLOR=#fe4fdd])[/COLOR], INDEX[COLOR=#fe4fdd]([/COLOR]List2, MATCH[COLOR=#0057d6]([/COLOR]0, COUNTIF[COLOR=#006107]([/COLOR][COLOR=#0057d6]$A$13:A13[/COLOR], List2[COLOR=#006107])[/COLOR], 0[COLOR=#0057d6])[/COLOR][COLOR=#fe4fdd])[/COLOR][COLOR=#ff9c1b])[/COLOR], INDEX[COLOR=#ff9c1b]([/COLOR]List3, MATCH[COLOR=#fe4fdd]([/COLOR]0, COUNTIF[COLOR=#0057d6]($A$13:A13[/COLOR], List3[COLOR=#0057d6])[/COLOR], 0[COLOR=#fe4fdd])[/COLOR][COLOR=#ff9c1b])[/COLOR][COLOR=#33af4a])[/COLOR], INDEX[COLOR=#33af4a]([/COLOR]List4, MATCH[COLOR=#ff9c1b]([/COLOR]0, COUNTIF[COLOR=#fe4fdd]([/COLOR][COLOR=#0057d6]$A$13:A13[/COLOR], List4[COLOR=#fe4fdd])[/COLOR], 0[COLOR=#ff9c1b])[/COLOR][COLOR=#33af4a])[/COLOR][COLOR=#a54a29])[/COLOR], INDEX[COLOR=#a54a29]([/COLOR]List5, MATCH[COLOR=#33af4a]([/COLOR]0, COUNTIF[COLOR=#ff9c1b]([/COLOR][COLOR=#0057d6]$A$13:A13[/COLOR], List5[COLOR=#ff9c1b])[/COLOR], 0[COLOR=#33af4a])[/COLOR][COLOR=#a54a29])[/COLOR][COLOR=#ab30d6])[/COLOR], INDEX[COLOR=#ab30d6]([/COLOR]List6, MATCH[COLOR=#a54a29]([/COLOR]0, COUNTIF[COLOR=#33af4a]([/COLOR][COLOR=#0057d6]$A$13:A13[/COLOR], List6[COLOR=#33af4a])[/COLOR], 0[COLOR=#a54a29])[/COLOR][COLOR=#ab30d6])[/COLOR][COLOR=#006107])[/COLOR], "")[/FONT][/COLOR]

 

[Jmoz092]

Bump

Does anyone have any suggestions for this? I need to display the data in the multi-column array from one sheet in a single column on a different sheet, without any blank cells. Thanks.

 

[Mike Szczesny]

Can you use this? It is not a perfect one step formula, but it gets to what you want. Assume list1 is in column A, list 2 in column B, etc. I first use a crazy complicated formula to display all the items (from all lists) in one row. The I use the the type of formula you are already using to get the unique items The first formula is [TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"] =IFERROR(INDEX($A$2:$C$10,INT(SMALL(IF($A$2:$C$10<>"",(ROW($A$2:$C$10)-ROW($A$2)+1)*10^9+COLUMN($A$2:$C$10)-COLUMN($A$2)+1),ROWS($E$2:E2))/10^9),MOD(SMALL(IF($A$2:$C$10<>"",(ROW($A$2:$C$10)-ROW($A$2)+1)*10^9+COLUMN($A$2:$C$10)-COLUMN($A$2)+1),ROWS($E$2:E2)),10^9)),"") You need to use Cntrl+Shift+Enter.

The second formula is [TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"] =INDEX($E$2:$E$22,MATCH(0,COUNTIF($F$1:F1,$E$2:$E$22),0))[/TD]
[/TR]
</tbody>[/TABLE]
Use Cntrl+Shift+Enter


[TABLE="width: 384"]
<colgroup><col width="64" span="6" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"]Unique[/TD]
[/TR]
[TR]
[TD]a[/TD]
[TD]a[/TD]
[TD]a[/TD]
[TD][/TD]
[TD]a[/TD]
[TD]a[/TD]
[/TR]
[TR]
[TD]b[/TD]
[TD]b[/TD]
[TD]l[/TD]
[TD][/TD]
[TD]a[/TD]
[TD]b[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]h[/TD]
[TD]m[/TD]
[TD][/TD]
[TD]a[/TD]
[TD]l[/TD]
[/TR]
[TR]
[TD]c[/TD]
[TD]i[/TD]
[TD]m[/TD]
[TD][/TD]
[TD]b[/TD]
[TD]h[/TD]
[/TR]
[TR]
[TD]d[/TD]
[TD]j[/TD]
[TD]o[/TD]
[TD][/TD]
[TD]b[/TD]
[TD]m[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]p[/TD]
[TD][/TD]
[TD]l[/TD]
[TD]c[/TD]
[/TR]
[TR]
[TD]e[/TD]
[TD]k[/TD]
[TD][/TD]
[TD][/TD]
[TD]h[/TD]
[TD]i[/TD]
[/TR]
[TR]
[TD]f[/TD]
[TD]k[/TD]
[TD]q[/TD]
[TD][/TD]
[TD]m[/TD]
[TD]d[/TD]
[/TR]
[TR]
[TD]g[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]c[/TD]
[TD]j[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]i[/TD]
[TD]o[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]m[/TD]
[TD]p[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]d[/TD]
[TD]e[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]j[/TD]
[TD]k[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]o[/TD]
[TD]f[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]p[/TD]
[TD]q[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]e[/TD]
[TD]g[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]k[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]f[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]k[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]q[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]g[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Jmoz092]

I'm having a hard time reading those equations and figuring out where your data is coming FROM, so that I can change it to suit my needs. My data is in:

'Reference'!A1:F101

Row 1 is a header row. All of the data that needs to be displayed in the single column is in each column, rows 2 to 101.

Thanks for the help

 

[Mike Szczesny]

I just used a simple data example for my formulas. The first list is A2:A10. The second list is B2:B10, etc. My first formula essentially includes all the ranges A2:C10. My first formula is in E2. My second formula is F2. The field containing Unique is in F1. The big number in my first formula is any big number you can use. It does not matter what that big number is. Just make big

 

[Jmoz092]

Thanks Mike, much appreciated! I'll toy around with it and post back.

 

[Jmoz092]

@Mike Szczesny, thank you. It works beautifully. I altered to fit my data set and it's listing everything that I need it to in one column. Thank you very much.

[COLOR=#000000][FONT=&quot]=IFERROR(INDEX[/FONT][/COLOR][COLOR=#006107][FONT=&quot]([/FONT][/COLOR][COLOR=#000000][FONT=&quot]'Reference'!$A$2:$F$101,INT[/FONT][/COLOR][COLOR=#AB30D6][FONT=&quot]([/FONT][/COLOR][COLOR=#000000][FONT=&quot]SMALL[/FONT][/COLOR][COLOR=#A54A29][FONT=&quot]([/FONT][/COLOR][COLOR=#000000][FONT=&quot]IF[/FONT][/COLOR][COLOR=#33AF4A][FONT=&quot]([/FONT][/COLOR][COLOR=#000000][FONT=&quot]'Reference'!$A$2:$F$101<>"",[/FONT][/COLOR][COLOR=#FF9C1B][FONT=&quot]([/FONT][/COLOR][COLOR=#000000][FONT=&quot]ROW[/FONT][/COLOR][COLOR=#FE4FDD][FONT=&quot]([/FONT][/COLOR][COLOR=#000000][FONT=&quot]'Reference'!$A$2:$F$101[/FONT][/COLOR][COLOR=#FE4FDD][FONT=&quot])[/FONT][/COLOR][COLOR=#000000][FONT=&quot]-ROW[/FONT][/COLOR][COLOR=#FE4FDD][FONT=&quot]([/FONT][/COLOR][COLOR=#000000][FONT=&quot]'Reference'!$A$2[/FONT][/COLOR][COLOR=#FE4FDD][FONT=&quot])[/FONT][/COLOR][COLOR=#000000][FONT=&quot]+1[/FONT][/COLOR][COLOR=#FF9C1B][FONT=&quot])[/FONT][/COLOR][COLOR=#000000][FONT=&quot]*10^9+COLUMN[/FONT][/COLOR][COLOR=#FF9C1B][FONT=&quot]([/FONT][/COLOR][COLOR=#000000][FONT=&quot]'Reference'!$A$2:$F$101[/FONT][/COLOR][COLOR=#FF9C1B][FONT=&quot])[/FONT][/COLOR][COLOR=#000000][FONT=&quot]-COLUMN[/FONT][/COLOR][COLOR=#FF9C1B][FONT=&quot]([/FONT][/COLOR][COLOR=#000000][FONT=&quot]'Reference'!$A$2[/FONT][/COLOR][COLOR=#FF9C1B][FONT=&quot])[/FONT][/COLOR][COLOR=#000000][FONT=&quot]+1[/FONT][/COLOR][COLOR=#33AF4A][FONT=&quot])[/FONT][/COLOR][COLOR=#000000][FONT=&quot],ROWS[/FONT][/COLOR][COLOR=#33AF4A][FONT=&quot]([/FONT][/COLOR][COLOR=#0057D6][FONT=&quot]$b$14:b14[/FONT][/COLOR][COLOR=#33AF4A][FONT=&quot])[/FONT][/COLOR][COLOR=#A54A29][FONT=&quot])[/FONT][/COLOR][COLOR=#000000][FONT=&quot]/10^9[/FONT][/COLOR][COLOR=#AB30D6][FONT=&quot])[/FONT][/COLOR][COLOR=#000000][FONT=&quot],MOD[/FONT][/COLOR][COLOR=#AB30D6][FONT=&quot]([/FONT][/COLOR][COLOR=#000000][FONT=&quot]SMALL[/FONT][/COLOR][COLOR=#A54A29][FONT=&quot]([/FONT][/COLOR][COLOR=#000000][FONT=&quot]IF[/FONT][/COLOR][COLOR=#33AF4A][FONT=&quot]([/FONT][/COLOR][COLOR=#000000][FONT=&quot]'Reference'!$A$2:$F$101<>"",[/FONT][/COLOR][COLOR=#FF9C1B][FONT=&quot]([/FONT][/COLOR][COLOR=#000000][FONT=&quot]ROW[/FONT][/COLOR][COLOR=#FE4FDD][FONT=&quot]([/FONT][/COLOR][COLOR=#000000][FONT=&quot]'Reference'!$A$2:$F$101[/FONT][/COLOR][COLOR=#FE4FDD][FONT=&quot])[/FONT][/COLOR][COLOR=#000000][FONT=&quot]-ROW[/FONT][/COLOR][COLOR=#FE4FDD][FONT=&quot]([/FONT][/COLOR][COLOR=#000000][FONT=&quot]'Reference'!$A$2[/FONT][/COLOR][COLOR=#FE4FDD][FONT=&quot])[/FONT][/COLOR][COLOR=#000000][FONT=&quot]+1[/FONT][/COLOR][COLOR=#FF9C1B][FONT=&quot])[/FONT][/COLOR][COLOR=#000000][FONT=&quot]*10^9+COLUMN[/FONT][/COLOR][COLOR=#FF9C1B][FONT=&quot]([/FONT][/COLOR][COLOR=#000000][FONT=&quot]'Reference'!$A$2:$F$101[/FONT][/COLOR][COLOR=#FF9C1B][FONT=&quot])[/FONT][/COLOR][COLOR=#000000][FONT=&quot]-COLUMN[/FONT][/COLOR][COLOR=#FF9C1B][FONT=&quot]([/FONT][/COLOR][COLOR=#000000][FONT=&quot]'Reference'!$A$2[/FONT][/COLOR][COLOR=#FF9C1B][FONT=&quot])[/FONT][/COLOR][COLOR=#000000][FONT=&quot]+1[/FONT][/COLOR][COLOR=#33AF4A][FONT=&quot])[/FONT][/COLOR][COLOR=#000000][FONT=&quot],ROWS[/FONT][/COLOR][COLOR=#33AF4A][FONT=&quot]([/FONT][/COLOR][COLOR=#0057D6][FONT=&quot]$b$14:b14[/FONT][/COLOR][COLOR=#33AF4A][FONT=&quot])[/FONT][/COLOR][COLOR=#A54A29][FONT=&quot])[/FONT][/COLOR][COLOR=#000000][FONT=&quot],10^9[/FONT][/COLOR][COLOR=#AB30D6][FONT=&quot])[/FONT][/COLOR][COLOR=#006107][FONT=&quot])[/FONT][/COLOR][COLOR=#000000][FONT=&quot],"")[SIZE=2][/SIZE][/FONT][/COLOR]