Arrange table by gathering data cells

**ElmerS** · 06-29-2009, 04:35 PM

Hi,

In the attached WB I managed to write some code (behind Sheet1) to change the Source table, itself, into the requested layout.

My question is - could this be done by ONLY Worksheets functions - preferable without any helper column - if possible.

Please note that all 4 columns data should be transported to the target table.

The upper left cell of the target/new layout table can be put in cell G1 or in cell A15.

Thanks, Elm

**DonkeyOte** · 06-29-2009, 05:19 PM

Assuming each name appears 3 times

G1:
=INDEX($A$2:$A$10,1+(3*(ROWS(G$1:G1)-1)))
copied down

H1:
=LOOKUP(REPT("Z",255),INDEX(B:B,MATCH($G1,$A:$A,0)):INDEX(B:B,MATCH($G1,$A:$A)))
copied down and applied also to Column J

I1:
as above but replace REPT("Z",255) with 9.99999999999999E+307

**ElmerS** · 06-29-2009, 05:42 PM

Thank you, DonkeOte,

This is nice and answers the question in full.

However, if I may:

a) What can be changed, if any, in the first formula to cope with various amounts of First Names - some will appear more or less than 3 times ?
b) I tried to replace the 3 "trios" - still ABC sorted - with:
ADAM
ADAM
ADAM
BENNI
BENNI
BENNI
CHARLIE
CHARLIE
CHARLIE
it continued to work fine, as expected.

But, when I changed the sorting order to:
ADAM
ADAM
ADAM
CHARLIE
CHARLIE
CHARLIE
BENNI
BENNI
BENNI

The results were incorrect.

Thanks, again, for your efforts and patient,

Elm

**DonkeyOte** · 06-29-2009, 05:49 PM

I will answer question 2 as it's important

For the formulae to work as they are the data in A should be sorted in Ascending order - it makes life easier in truth - if this isn't viable you can work around it but I would advise sorting ABC.

Re: question 1

If willing to have G1 with a different formula to G2 you can set

G1: =A2
G2: =INDEX($A$2:$A$100,1+COUNTIF($A$2:$A$100,"<="&G1))
(change ranges to suit)