# Rearrangement of 2 Dimensional Data based on Lookup

1. ## Rearrangement of 2 Dimensional Data based on Lookup

Hi everyone,

I intend to re-arrange data in Columns B-E, and place it in the format Columns H-L (as shown below).

Eg. Cell I4 was placed there because its data contains "H1" and was found under "CodeA"

1.jpg

How would you achieve the end result?

Thanks.

2. ## Re: Rearrangement of 2 Dimensional Data based on Lookup

I'm having trouble opening the file, could you please re-attach?

3. ## Re: Rearrangement of 2 Dimensional Data based on Lookup

Does this file work?

4. ## Re: Rearrangement of 2 Dimensional Data based on Lookup

Yes, I'll have a look and see if I can help

5. ## Re: Rearrangement of 2 Dimensional Data based on Lookup

sorry I can only seem to currently get part working which is multiple results for one criteria listed, I have meetings this afternoon so I can't look again until tomorrow.

Formula:
`Please Login or Register  to view this content.`

Is my current result used in Cell I4 then dragged to fill table.

6. ## Re: Rearrangement of 2 Dimensional Data based on Lookup

Because of that, I learnt about the AGGREGATE function.

What does this portion in the aggregate formula mean?
ROW(\$1:\$10)/(\$A\$4:\$A\$10=\$H4),COLUMN(A\$4:A\$10)

Also, does anyone know how to rectify the above formula to generate the intended result?
1.jpg

Thanks.

7. ## Re: Rearrangement of 2 Dimensional Data based on Lookup

I can not think of a way to do this formula without redimensioning the source range. This formula is quite convoluted. Hopefully some one will come along with a simpler or shorter formula.

I borrowed shamelessly from XOR LX's website and someone named Lori.

Since you have Excel 2013 this works.

There is a named formula in Name Manager I called AddrArry You need to have cell I4 the active cell when you define this.

Then array enter this formula in I4 and copy across and down. If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.
Formula:
`Please Login or Register  to view this content.`

8. ## Re: Rearrangement of 2 Dimensional Data based on Lookup

This has a shorter definition in Name Manager.

The formula in I4 is still array entered. It's the same as before.
Formula:
`Please Login or Register  to view this content.`

9. ## Re: Rearrangement of 2 Dimensional Data based on Lookup

It turns out this doesn't have to be redimensioned, nor does it need named formulas in Name Manager.

Array enter this in I4 fill down and across.

Formula:
`Please Login or Register  to view this content.`

10. ## Re: Rearrangement of 2 Dimensional Data based on Lookup

Also, in I4:

=IFERROR(INDIRECT(TEXT(AGGREGATE(15,6,(ROW(\$B\$4:\$E\$10)*10^5+COLUMN(\$B\$4:\$E\$10))/((\$A\$4:\$A\$10=\$H4)*ISNUMBER(FIND(I\$3&"-",\$B\$4:\$E\$10))),1),"R0C00000"),0),"")

Originally Posted by FlameRetired
I borrowed shamelessly from XOR LX's website and someone named Lori.

Regards

11. ## Re: Rearrangement of 2 Dimensional Data based on Lookup

@ XOR LX,

Thank you. I feel exonerated!

12. ## Re: Rearrangement of 2 Dimensional Data based on Lookup

Originally Posted by XOR LX
Also, in I4:

=IFERROR(INDIRECT(TEXT(AGGREGATE(15,6,(ROW(\$B\$4:\$E\$10)*10^5+COLUMN(\$B\$4:\$E\$10))/((\$A\$4:\$A\$10=\$H4)*ISNUMBER(FIND(I\$3&"-",\$B\$4:\$E\$10))),1),"R0C00000"),0),"")
Yes. Thank you.

I just couldn't resist using the new DECIMAL and BASE functions (new to Excel 2013 anyway). Then there was use of the base 36 numbering system. Wow! Who would of 'thunk' it?

Hats off to Lori and your fine site!

13. ## Re: Rearrangement of 2 Dimensional Data based on Lookup

And one more shorter still.

Still array entered.

Formula:
`Please Login or Register  to view this content.`

14. ## Re: Rearrangement of 2 Dimensional Data based on Lookup

Thanks for all your solutions, PFDave, FlameRetired and XOR LX. I have learnt many new functions from each of you.

15. ## Re: Rearrangement of 2 Dimensional Data based on Lookup

You're welcome. Thanks for the feedback, and thank you for marking your thread [SOLVED].

There are currently 1 users browsing this thread. (0 members and 1 guests)