Consolidating data into one cell without a module as it runs to slow

[wrighty998]

my base data with assigned duplicate id

[TABLE="class: grid, width: 1208"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD]ID
[/TD]
[TD]AREA[/TD]
[TD]CUSTOMER[/TD]
[TD]ADDRESS[/TD]
[TD]NUMBER[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Delivery Area: 01 - Glasgow / Edinburgh[/TD]
[TD]1139 - apple
[/TD]
[TD]zzzz
[/TD]
[TD]893582
[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Delivery Area: 01 - Glasgow / Edinburgh[/TD]
[TD]1139 - apple
[/TD]
[TD]zzzz
[/TD]
[TD]895980
[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Delivery Area: 01 - Glasgow / Edinburgh[/TD]
[TD]1139 - apple
[/TD]
[TD]zzzz
[/TD]
[TD]896039
[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Delivery Area: 01 - Glasgow / Edinburgh[/TD]
[TD]1139 - apple
[/TD]
[TD]zzzz
[/TD]
[TD]897039
[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Delivery Area: 01 - Glasgow / Edinburgh[/TD]
[TD]1139 - apple
[/TD]
[TD]zzzz
[/TD]
[TD]897359
[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Delivery Area: 01 - Glasgow / Edinburgh[/TD]
[TD]1139 - apple
[/TD]
[TD]zzzz
[/TD]
[TD]897617
[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Delivery Area: 01 - Glasgow / Edinburgh[/TD]
[TD]1139 - apple
[/TD]
[TD]zzzz
[/TD]
[TD]897620
[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Delivery Area: 01 - Glasgow / Edinburgh[/TD]
[TD]1139 - apple
[/TD]
[TD]zzzz
[/TD]
[TD]897646
[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Delivery Area: 01 - Glasgow / Edinburgh[/TD]
[TD]1139 - apple
[/TD]
[TD]zzzz
[/TD]
[TD]897704
[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Delivery Area: 01 - Glasgow / Edinburgh[/TD]
[TD]1139 - apple
[/TD]
[TD]zzzz
[/TD]
[TD]897706
[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Delivery Area: 01 - Glasgow / Edinburgh[/TD]
[TD]1139 - apple
[/TD]
[TD]zzzz
[/TD]
[TD]897875
[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Delivery Area: 01 - Glasgow / Edinburgh[/TD]
[TD]1139 - apple
[/TD]
[TD]zzzz
[/TD]
[TD]896121
[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Delivery Area: 01 - Glasgow / Edinburgh[/TD]
[TD]1299 - banana
[/TD]
[TD]xxxx
[/TD]
[TD]895741
[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Delivery Area: 01 - Glasgow / Edinburgh[/TD]
[TD]1299 - banana
[/TD]
[TD]xxxx
[/TD]
[TD]895883
[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Delivery Area: 01 - Glasgow / Edinburgh[/TD]
[TD]1299 - banana
[/TD]
[TD]xxxx
[/TD]
[TD]896119
[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Delivery Area: 01 - Glasgow / Edinburgh[/TD]
[TD]1299 - banana
[/TD]
[TD]xxxx
[/TD]
[TD]896163
[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Delivery Area: 01 - Glasgow / Edinburgh[/TD]
[TD]2798 - orange
[/TD]
[TD]cccc
[/TD]
[TD]895962
[/TD]
[/TR]
</tbody>[/TABLE]


my data sorted from id using index match

[TABLE="class: grid, width: 1398"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD]ID
[/TD]
[TD]AREA[/TD]
[TD]CUSTOMER[/TD]
[TD]ADDRESS[/TD]
[TD]NUMBER[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Delivery Area: 01 - Glasgow / Edinburgh[/TD]
[TD]1139 - apple
[/TD]
[TD]zzzz
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Delivery Area: 01 - Glasgow / Edinburgh[/TD]
[TD]1299 - banana
[/TD]
[TD]xxxx
[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Delivery Area: 01 - Glasgow / Edinburgh[/TD]
[TD]2798 - orange
[/TD]
[TD]cccc
[/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]


only bit i cant get working is match the id number and consolade the number into the one cell like this

[TABLE="class: grid, width: 500"]
<colgroup><col style="width:48pt" width="64"> <col style="mso-width-source:userset;mso-width-alt:8630;width:177pt" width="236"> <col style="mso-width-source:userset;mso-width-alt:9691;width:199pt" width="265"> <col style="mso-width-source:userset;mso-width-alt:23808;width:488pt" width="651"> <col style="mso-width-source:userset;mso-width-alt:6656;width:137pt" width="182"> </colgroup><tbody>[TR]
[TD="width: 64"]ID
[/TD]
[TD="width: 236"]AREA[/TD]
[TD="width: 265"]CUSTOMER[/TD]
[TD="width: 651"]ADDRESS[/TD]
[TD="width: 182"]NUMBER[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Delivery Area: 01 - Glasgow / Edinburgh[/TD]
[TD]1139 - apple
[/TD]
[TD]zzzz
[/TD]
[TD]893582 895980 896039 897039 897617 897620 897646 897704 897706 897875 896121
[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Delivery Area: 01 - Glasgow / Edinburgh
[/TD]
[TD]1299 - banana
[/TD]
[TD]xxxx
[/TD]
[TD]895741 895883 896119 896163
[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Delivery Area: 01 - Glasgow / Edinburgh[/TD]
[TD]2798 - orange
[/TD]
[TD]cccc
[/TD]
[TD] 895962
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Mike Szczesny]

Can you use this non-macro formula? [TABLE="width: 75"]
<tbody>[TR]
[TD="width: 75"] =SUBSTITUTE(CONCATENATE(TRANSPOSE(IF($B$2:$B$11=$B$13,$C$2:$C$11&CHAR(10)))),"FALSE",""). The formula first concatenates all the results in a cell from left to right. The concatenation along with Char (10) formula will produce "FALSE" results for those numbers which do not apply to "apple". The substitute part removes the "FALSE" with a blank. Before you enter, you need to highlight the transpose(.......to the 2nd last ) after char(10) with your mouse. Hit F9 and remove both { and }. Hit enter and then go to format cell and select wrap text.

[TABLE="width: 206"]
<colgroup><col span="2"><col></colgroup><tbody>[TR]
[TD]ID[/TD]
[TD]Cust[/TD]
[TD]Number[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]apple[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]apple[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]apple[/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]apple[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]apple[/TD]
[TD]25[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]apple[/TD]
[TD]30[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]banana[/TD]
[TD]35[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]banana[/TD]
[TD]40[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]banana[/TD]
[TD]45[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]ornge[/TD]
[TD]50[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]apple[/TD]
[TD]5
10
15
20
25
30
[/TD]
[/TR]
</tbody>[/TABLE]


[/TD]
[/TR]
</tbody>[/TABLE]

 

[wrighty998]

Re: PLEASE HELP!! consolading data into one cell without a module as it runs to slow

Do you mean that part of the header is getting copied to the end of the data?

all working great thanks pal

 

[Fluff]

Re: PLEASE HELP!! consolading data into one cell without a module as it runs to slow

Glad we could help & thanks for the feedback