Excel Data Consolidation
Page 2 of 3 FirstFirst 123 LastLast
Results 11 to 20 of 21

Thread: Excel Data Consolidation
Thanks Thanks: 0 Likes Likes: 0

  1. #11
    New Member
    Join Date
    Aug 2009
    Posts
    15
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excel Data Consolidation

    Inserted given code by right clicking sheet - view code.

    Formula in K2 gives error #NAME ?

    What about I2 ?

  2. #12
    Board Regular Sam_D_Ben's Avatar
    Join Date
    Oct 2012
    Location
    New Jersey, USA
    Posts
    371
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excel Data Consolidation

    Not view code. Its Developer tab -> Visual Basic -> Insert Module

    If its F2, added an additional column.
    Last edited by Sam_D_Ben; Aug 13th, 2019 at 11:07 AM.
    Sam_D_Ben

  3. #13
    New Member
    Join Date
    Aug 2009
    Posts
    15
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excel Data Consolidation

    Thanks for the help, Great Support.

    Initially I did figure out a following formula for I2
    =INDEX($A$2:$A$50,MATCH(MAX(IF($B$2:$B$50=$H2,$E$2:$E$50)),$E$2:$E$50,0))

    But i realized that this formula gives wrong result if duplicate values found. So after lots of research and experiment, somehow I got a formula (given below) that works without any issues.

    Formula for I2

    =INDEX($A$2:$A$50,MATCH(MAX(IF($B$2:$B$50=$H2,$E$2:$E$50)),IF($B$2:$B$50=$H2,$E$2:$E$50),0))

    This one works perfectly.

    Thanks again

  4. #14
    New Member
    Join Date
    Aug 2009
    Posts
    15
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excel Data Consolidation

    One last concern, Your VBA code doesnt have any range mentioned in it. I posted a sample (example) sheet for ease of understanding my query. My actual sheet has lots of column and rows. Will this VBA code work in actual sheet too ?

  5. #15
    Board Regular Sam_D_Ben's Avatar
    Join Date
    Oct 2012
    Location
    New Jersey, USA
    Posts
    371
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excel Data Consolidation

    Yes, It will. You can modify the formula according to your requirement.
    Sam_D_Ben

  6. #16
    New Member
    Join Date
    Aug 2009
    Posts
    15
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excel Data Consolidation

    Code is fine but its not updating automatically as i change source data. Please make it auto-update.

  7. #17
    New Member
    Join Date
    Aug 2009
    Posts
    15
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excel Data Consolidation

    What about TEXTJOIN function in K2, will that be feasible ?

  8. #18
    Board Regular Sam_D_Ben's Avatar
    Join Date
    Oct 2012
    Location
    New Jersey, USA
    Posts
    371
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excel Data Consolidation

    Yes TEXTJOIN works. but my excel is not the updated version.
    Sam_D_Ben

  9. #19
    New Member
    Join Date
    Aug 2009
    Posts
    15
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excel Data Consolidation

    Update :
    While experimenting, i found below formula for K2,

    =TEXTJOIN(" | ",,IF(($B$2:$B$11=H2)*($F$2:$F$11<>"")=1,$F$2:$F$11,""))

    its an array formula, so ctrl + shift + enter, I dont know if this one is perfect or not, am still experimenting.

  10. #20
    Board Regular Sam_D_Ben's Avatar
    Join Date
    Oct 2012
    Location
    New Jersey, USA
    Posts
    371
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excel Data Consolidation

    Great, Progressive.
    Sam_D_Ben

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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