Is there any way to Sort and Separate into columns?

[CriticalMass048]

I hope I can explain well what I want to do, so I'll try to make it simple:

Is there way way I can have Excel look at a cell (let's say "B2"), and based on the value of that cell, copy a range of cells (let's say A2:D2) into maybe F2:I2, J2:M2, etc?

For instance, if I had a list...

ITEM TYPE PRICE
Apple Fruit .49
Pear Fruit .59
Corn Veg .69
Eggs Dairy 2.50
Tomato Veg .79

...and then I could separate the categories put all three (A:C) Fruit cells in E:G, Veggie cells in H:J, Dairy in K:M, etc.

Thanks!

[ChemistB]

I think you're looking for a pivot table solution (See attachment). If this is not what you are looking for, try uploading an example workbook as described in the yellow banner at the top of the page. Let us know if you have any questions.

[Glenn Kennedy]

If not, please read the yellow banner (top of page) and provide a sample sheet for us to play with.

[CriticalMass048]

I was thinking more along the lines of this

[Glenn Kennedy]

One way, in E2, copied across and down:

=IFERROR(INDEX($A:$C,AGGREGATE(15,6,ROW($B$3:$B$100)/($B$3:$B$100=INDEX($E$1:$M$1,1+3*INT((COLUMNS($E2:E2)-1)/3))),ROWS(E$2:E2)),1+MOD((COLUMNS($E2:E2)-1),3)),"")

[CriticalMass048]

That looks great! I don't understand it at all, but after I search for INDEX, AGGREGATE, and COLUMNS and educate myself, hopefully I will grasp it better! Thanks!

[Glenn Kennedy]

At its heart is a simple enough formula:

=INDEX($A:$A,AGGREGATE(15,6,ROW($B$3:$B$100)/($B$3:$B$100=$E$1),ROWS(E$2:E2)))

Red return these row numbers
Orange where these conditions are met
Green in ascending order
Blue starting from the lowest (a counter... 1, 2, 3, etc)
Black finally returning the corresponding values from column A.

The rest just expands it by adding on two additional counters to select the correct columns and restart numbering as required. I have reposted the file (with the solutiuon in place, this time, but covering ONLY the exact number of rows required.

Now use formulas/evaluate formula to step through the formula to see exactly what it's doing.




You're welcome.

---

It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

Finally, if that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.