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

1. ## Re: Excel Data Consolidation

Inserted given code by right clicking sheet - view code.

Formula in K2 gives error #NAME ?

2. ## Re: Excel Data Consolidation

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

3. ## 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. ## 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. ## Re: Excel Data Consolidation

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

6. ## Re: Excel Data Consolidation

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

7. ## Re: Excel Data Consolidation

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

8. ## Re: Excel Data Consolidation

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

9. ## 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. ## Re: Excel Data Consolidation

Great, Progressive.