Consolidate and feed information across worksheets with VBA (example included)

[Kate]

Hi all,

I am struggling with writing a formula to consolidate rows tied with a key value, and have them feed into a higher-level report.

It is a two-part problem. The sheet first needs to consolidate rows columns that have the same identifier number, across 8 columns. These then need to pull into a second spreadsheet page.

Additionally, there are descriptions for each of the rows. The sheet needs to compile all the descriptions with the same identifier number, and report that into a second sheet as well.

I hope I described that well. I attached a spreadsheet to better illustrate what I'm trying to say.

Do you have any insight as to how to get VBA to write this? I've been playing with loop statements for the last few hours, and am stuck.

Thank you for any help/tips!
Kate

 

[NARAYANK991]

Hi Kate ,

See if this is acceptable. It makes use of a macro written by Rick Rothstein , available here :

http://www.excelfox.com/forum/f22/lookup-value-and-concatenate-all-found-results-345/

Narayan

 

[SirJB7]

Hi, Kate!

Give a look at this file:
https://dl.dropboxusercontent.com/u/60558749/Consolidate and feed information across worksheets with VBA (example included) - Demo Sheet (for Kate at chandoo.org).xlsx

It's a formula only solution.

It uses 2 helper columns at the right of detail table:
N6: =SI(C6=C7;0;MAX(N$5:N5)+1) -----> in english: =IF(C6=C7,0,MAX(N$5:N5)+1)
O6: =SI(C6=C5;O5&CARACTER(10);"")&D6 -----> in english: =IF(C6=C5,O5&CHAR(10),"")&D6
Copy down as required.

In the summary table:
B6: =FILA()-5 -----> in english: =ROW()-5
C6: =INDICE('3a. Details'!O$6:O$17;COINCIDIR(B6;'3a. Details'!N$6:N$17;0)) -----> in english: =INDEX('3a. Details'!O$6:O$17,MATCH(B6,'3a. Details'!N$6:N$17,0))
D6: =SI(CONTARA(INDIRECTO(DIRECCION(COINCIDIR($B6;'3a. Details'!$C$6:$C$17;0)+5;COLUMNA()+2;4;1;"3a. Details")&":"&DIRECCION(COINCIDIR($B6;'3a. Details'!$N$6:$N$17;0)+5;COLUMNA()+2;4;1)))>0;"X";"") -----> in english: =IF(COUNTA(INDIRECT(ADDRESS(MATCH($B6,'3a. Details'!$C$6:$C$17,0)+5,COLUMN()+2,4,1,"3a. Details")&":"&ADDRESS(MATCH($B6,'3a. Details'!$N$6:$N$17,0)+5,COLUMN()+2,4,1)))>0,"X","")
Copy across D6 as required (thru K6).
Copy down B6:K6 as required (thru row 10)

Just advise if any issue.

Regards!

 

[Kate]

Thank you both! The VBA in the link was a bit above my level, but the formula works perfectly!

 

[SirJB7]

Hi, !
Glad you solved it. Thanks for your feedback and welcome back whenever needed or wanted.
Despite of this, when you want to give a try and analyze that VBA code, just say "Hi!" and surely someone will offer you a guided tour thru it, it's not so long so as to get lost and maybe you end feeling fine and more confident after doing it.
Regards!