formula to create a 1 list (not unique) from 4 columns

[FreakyGirl]

hello

i have 4 columns of names i want to combine into 1 list including duplicates.

the current formula i have creates a unique list from those 4 columns as new names are added in but it slows down my sheet & i dont need this new list to change on the fly.

im not sure how to adjust my current formula to fit my requirements.

=IFERROR(IFERROR(IFERROR(IFERROR(INDEX(A$3:A$503, MATCH(0, COUNTIF($F$2:F2, A$3:A$503), 0)), INDEX(B$3:B$503, MATCH(0, COUNTIF($F$2:F2, B$3:B$503), 0))), INDEX(C$3:C$503, MATCH(0, COUNTIF($F$2:F2, C$3:C$503), 0))), INDEX(D$3:D$503, MATCH(0, COUNTIF($F$2:F2, D$3:D$503), 0))), "")CODE]

thanks,
FreakyGirl

 

[r2c2]

@FreakyGirl

Interesting question. In an ideal world, you would use Power Query to zap all this data in to one column. I am guessing you have strong reasons to not use it. So here goes the formula.

=INDEX($A$3:$D$313,MOD(ROWS($A$3:A3)-1,ROWS($A$3:$D$313))+1,INT((ROWS($A$3:A3)-1)/ROWS($A$3:$D$313))+1)

Just replace all $A$3:$D$313 with actual data range or better still give your raw data a name like data and you could use this formula.

=INDEX(data,MOD(ROWS($A$3:A3)-1,ROWS(data))+1,INT((ROWS($A$3:A3)-1)/ROWS(data))+1)

This should work for any number of columns and any size of data (but only for rectangular data, if each column has different number of cells then you need something else).

It would throw #REF! errors when you drag beyond the number of cells available. Just wrap this with IFERROR or don't over drag.

 

[vletm]

FreakyGirl
... or press [ Do It ] -button
and get the (Sorted) Result.

 

[Khalid NGO]

Hi to all,
Another option, similar to r2c2

=OFFSET(A$3,MOD(ROWS(A$3:A3)-1,COUNTA(A:A)-1),INT((ROWS($A$3:A3)-1)/ROWS($A$3:$D$313)))

Regards,

 

[FreakyGirl]

hello

thanks so much for all the options! they worked great!

love this forum!
FreakyGirl