Create a list based on two sets of columns ... with a twist

[kriderc]

Hi, I'm new here and I hope this query isn't too easy but I couldn't figure it out.

I have 2 columns of data e.g.
Column A
1
2
3

Column B
textA
textB
textC
textD
textE

What I need is to generate a list of 2 columns side by side like this
1 | textA
1 | textB
1 | textC
1 | textD
1 | textE
2 | textA
2 | textB
2 | textC
2 | textD
2 | textE
3 | textA
3 | textB
3 | textC
3 | textD
3 | textE

i.e. first cell based on Column A and second cell based on Column B, then value in B reiterates when value in A changes etc.

Any help appreciated.

Br, Kr

[José Augusto]

Hi
Try this
=INDIRECT("A"&QUOTIENT(ROW(A1)-1,COUNTA($B:$B))+1)&INDIRECT("B"&IF(MOD(ROW(A1),COUNTA($B:$B))=0,10,MOD(ROW(A1),COUNTA($B:$B))))

Please Login or Register  to view this content.

Copy down
Regards

[JohnTopley]

Assuming data starts in row 1 of columns A and B

In C1

=INDIRECT("A"&(INT((ROW()-1)/COUNTA($B$1:$B$10))+1))

in D1

=INDIRECT("B"&(ROW()-INT((ROW()-1)/COUNTA($B$1:$B$10))*COUNTA($B$1:$B$10)))

Adjust highlighted range in more than 10- entries

Copy both formulas down

[kriderc]

Thanks a million José ... this certainly could be built on for me.

[kriderc]

Hi John,

You are a Legend! This is exactly what I needed. This is going to save me so much time going forward.

Much respect and thanks.
-Kr

[JohnTopley]

Thanks for the feedback.

If you are OK with the solution, could you please mark the thread as SOLVED ("Thread Tools" at top of first post).