+ Reply to Thread
Results 1 to 15 of 15

Rearrangement of 2 Dimensional Data based on Lookup

  1. #1
    Registered User
    Join Date
    03-10-2017
    Location
    APAC
    MS-Off Ver
    2013
    Posts
    10

    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. #2
    Valued Forum Contributor PFDave's Avatar
    Join Date
    05-17-2012
    Location
    Milton Keynes, England
    MS-Off Ver
    Excel 2013
    Posts
    1,067

    Re: Rearrangement of 2 Dimensional Data based on Lookup

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

  3. #3
    Registered User
    Join Date
    03-10-2017
    Location
    APAC
    MS-Off Ver
    2013
    Posts
    10

    Re: Rearrangement of 2 Dimensional Data based on Lookup

    Does this file work?
    Attached Files Attached Files

  4. #4
    Valued Forum Contributor PFDave's Avatar
    Join Date
    05-17-2012
    Location
    Milton Keynes, England
    MS-Off Ver
    Excel 2013
    Posts
    1,067

    Re: Rearrangement of 2 Dimensional Data based on Lookup

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

  5. #5
    Valued Forum Contributor PFDave's Avatar
    Join Date
    05-17-2012
    Location
    Milton Keynes, England
    MS-Off Ver
    Excel 2013
    Posts
    1,067

    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: copy to clipboard
    Please Login or Register  to view this content.


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

  6. #6
    Registered User
    Join Date
    03-10-2017
    Location
    APAC
    MS-Off Ver
    2013
    Posts
    10

    Re: Rearrangement of 2 Dimensional Data based on Lookup

    Thanks for your effort, PFDave.
    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.
    Attached Files Attached Files

  7. #7
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    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.

    Advanced formula challenge #13

    https://excelxor.com/2016/04/08/adva...le-worksheets/

    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.

    =BASE(LARGE(DECIMAL(IFERROR(ADDRESS(IF((Sheet1!$H4=Sheet1!$A$4:$A$10)*(Sheet1!$B$4:$E$10<>""),ROW(Sheet1!$B$4:$E$10)),IF((Sheet1!$H4=Sheet1!$A$4:$A$10)*(Sheet1!$B$4:$E$10<>""),COLUMN(Sheet1!$B$4:$E$10)),4),""),36),ROW(INDIRECT("1:"&SUM(IF(COUNTIF(Sheet1!$H4,Sheet1!$A$4:$A$10),COUNTIF(Sheet1!$B$4:$E$10,Sheet1!$B$4:$E$10)))))),36)

    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: copy to clipboard
    Please Login or Register  to view this content.
    Dave

  8. #8
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Rearrangement of 2 Dimensional Data based on Lookup

    This has a shorter definition in Name Manager.

    =BASE(MODE.MULT(IF(Sheet1!$H4=Sheet1!$A$4:$A$10,DECIMAL(ADDRESS(ROW(Sheet1!$B$4:$E$10),COLUMN(Sheet1!$B$4:$E$10),4),36)),IF(Sheet1!$H4=Sheet1!$A$4:$A$10,DECIMAL(ADDRESS(ROW(Sheet1!$B$4:$E$10),COLUMN(Sheet1!$B$4:$E$10),4),36))),36)

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

  9. #9
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    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: copy to clipboard
    Please Login or Register  to view this content.

  10. #10
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    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),"")

    Quote Originally Posted by FlameRetired View Post
    I borrowed shamelessly from XOR LX's website and someone named Lori.
    Nothing shameless about that!

    Regards
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

  11. #11
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Rearrangement of 2 Dimensional Data based on Lookup

    @ XOR LX,

    Nothing shameless about that!
    Thank you. I feel exonerated!
    Last edited by FlameRetired; 04-12-2017 at 08:04 PM.

  12. #12
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Rearrangement of 2 Dimensional Data based on Lookup

    Quote Originally Posted by XOR LX View Post
    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. #13
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Rearrangement of 2 Dimensional Data based on Lookup

    And one more shorter still.

    Still array entered.

    Formula: copy to clipboard
    Please Login or Register  to view this content.

  14. #14
    Registered User
    Join Date
    03-10-2017
    Location
    APAC
    MS-Off Ver
    2013
    Posts
    10

    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. #15
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Rearrangement of 2 Dimensional Data based on Lookup

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

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Data rearrangement
    By akumagaavese in forum Excel General
    Replies: 15
    Last Post: 08-21-2017, 07:06 AM
  2. Data rearrangement via VBA
    By Andy308 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-04-2016, 03:57 PM
  3. data rearrangement problem
    By coralnerd in forum Excel General
    Replies: 2
    Last Post: 08-02-2015, 09:46 PM
  4. [SOLVED] populate worksheet cell based on two dimensional lookup values of combobox
    By rain4uu in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 12-27-2014, 03:05 AM
  5. [SOLVED] Data rearrangement
    By akumagaavese in forum Excel General
    Replies: 3
    Last Post: 08-26-2014, 04:02 PM
  6. Data Rearrangement
    By nasoor123 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-19-2010, 03:02 PM
  7. data rearrangement...???
    By nkk in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-12-2006, 05:30 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1