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

W

wrighty998

New Member
Joined
Jan 31, 2018
Messages
35
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]
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
Sort by date Sort by votes
Re: PLEASE HELP!! consolading data into one cell without a module as it runs to slow

Whilst You've said that a macro is to slow, try this
Code: 
Sub ConcatDupes()

   Dim Cl As Range
   Dim Itm As Variant
   Dim OrigWs As Worksheet
   Dim DestWs As Worksheet
   
   Set OrigWs = Sheets("[COLOR=#ff0000]Records[/COLOR]")
   Set DestWs = Sheets("[COLOR=#ff0000]Sheet3[/COLOR]")
Application.ScreenUpdating = False
   With CreateObject("scripting.dictionary")
      For Each Cl In OrigWs.Range("A2", OrigWs.Range("A" & Rows.Count).End(xlUp))
         If Not .exists(Cl.Value) Then
            .Add Cl.Value, Array(Cl, Cl.Offset(, 4).Value)
         Else
            .Item(Cl.Value) = Array(.Item(Cl.Value)(0), .Item(Cl.Value)(1) & vbLf & Cl.Offset(, 4).Value)
         End If
      Next Cl
      For Each Itm In .items
         DestWs.Range("A" & Rows.Count).End(xlUp).Offset(1).Resize(, 4).Value = Itm(0).Resize(, 4).Value
         DestWs.Range("E" & Rows.Count).End(xlUp).Offset(1).Value = Itm(1)
         DestWs.Rows(1).Value = OrigWs.Rows(1).Value
      Next Itm
   End With
End Sub
 
Last edited:
Upvote 0
Re: PLEASE HELP!! consolading data into one cell without a module as it runs to slow

Fluff said:
Whilst You've said that a macro is to slow, try this
Code: 
Sub ConcatDupes()

   Dim Cl As Range
   Dim Itm As Variant
   Dim OrigWs As Worksheet
   Dim DestWs As Worksheet
   
   Set OrigWs = Sheets("Records")
   Set DestWs = Sheets("Sheet3")
Application.ScreenUpdating = False
   With CreateObject("scripting.dictionary")
      For Each Cl In OrigWs.Range("A2", OrigWs.Range("A" & Rows.Count).End(xlUp))
         If Not .exists(Cl.Value) Then
            .Add Cl.Value, Array(Cl, Cl.Offset(, 4).Value)
         Else
            .Item(Cl.Value) = Array(.Item(Cl.Value)(0), .Item(Cl.Value)(1) & vbLf & Cl.Offset(, 4).Value)
         End If
      Next Cl
      For Each Itm In .items
         DestWs.Range("A" & Rows.Count).End(xlUp).Offset(1).Resize(, 4).Value = Itm(0).Resize(, 4).Value
         DestWs.Range("E" & Rows.Count).End(xlUp).Offset(1).Value = Itm(1)
         DestWs.Rows(1).Value = OrigWs.Rows(1).Value
      Next Itm
   End With
End Sub
Click to expand...



ill give that a try mate what do i type in the cell to use this ??

=Lookup_concat(A2,COPY!A:A,COPY!E:E)???:confused:
 
Upvote 0
Re: PLEASE HELP!! consolading data into one cell without a module as it runs to slow

First off, I forgot to mention that you need to change the sheet names to match your sheet names (I've now highlighted them in the original code).
To run the macro simply press Alt F8, select ConcatDupes, from the list that appears & click Run
 
Upvote 0
wrighty998 said:

[TABLE="class: grid, width: 500"]
<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]
Click to expand...

A few questions...

1) Are the AREA, CUSTOMER and ADDRESS always the identical for each individual ID number as shown above? In other words, will ID #1 ever have two or more different CUSTOMER names?

2) What is the sheet name and column designations (Columns A:D maybe) for your original data (top posted table)?

3) What is the sheet name and column designations for the output (bottom posted) table?
 
Upvote 0
Re: PLEASE HELP!! consolading data into one cell without a module as it runs to slow

Fluff said:
First off, I forgot to mention that you need to change the sheet names to match your sheet names (I've now highlighted them in the original code).
To run the macro simply press Alt F8, select ConcatDupes, from the list that appears & click Run
Click to expand...

THAT IS MINT!!!!

wow thats so quick i love it, only thing is it needs a some sort of spacing between the numbers a comma would be best prob is that possible mate?
 
Upvote 0
Re: PLEASE HELP!! consolading data into one cell without a module as it runs to slow

At the moment the code puts a new line between the values, but if youl'd prefer a comma, try this
Code: 
Sub ConcatDupes()

   Dim Cl As Range
   Dim Itm As Variant
   Dim OrigWs As Worksheet
   Dim DestWs As Worksheet
   
   Set OrigWs = Sheets("Records")
   Set DestWs = Sheets("Sheet3")
Application.ScreenUpdating = False
   With CreateObject("scripting.dictionary")
      For Each Cl In OrigWs.Range("A2", OrigWs.Range("A" & Rows.Count).End(xlUp))
         If Not .exists(Cl.Value) Then
            .Add Cl.Value, Array(Cl, Cl.Offset(, 4).Value)
         Else
            .Item(Cl.Value) = Array(.Item(Cl.Value)(0), .Item(Cl.Value)(1) &[COLOR=#ff0000] ", "[/COLOR] & Cl.Offset(, 4).Value)
         End If
      Next Cl
      For Each Itm In .items
         DestWs.Range("A" & Rows.Count).End(xlUp).Offset(1).Resize(, 4).Value = Itm(0).Resize(, 4).Value
         DestWs.Range("E" & Rows.Count).End(xlUp).Offset(1).Value = Itm(1)
         DestWs.Rows(1).Value = OrigWs.Rows(1).Value
      Next Itm
   End With
End Sub
 
Upvote 0
Rick Rothstein said:
A few questions...

1) Are the AREA, CUSTOMER and ADDRESS always the identical for each individual ID number as shown above? In other words, will ID #1 ever have two or more different CUSTOMER names?

2) What is the sheet name and column designations (Columns A:D maybe) for your original data (top posted table)?

3) What is the sheet name and column designations for the output (bottom posted) table?
Click to expand...

1. no only one name pal its uses index match against the id numbers from 'copy' to consolidate data into 'schdeule' e.g =INDEX(COPY!B:B, MATCH(SCHDEULE!A2, COPY!A:A,0)) just struggling to bring number together into one cell

2. sheet 'COPY' a,b,c,d,e

3. sheet 'SCHDEULE' a,b,c,d,e

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

Fluff said:
At the moment the code puts a new line between the values, but if youl'd prefer a comma, try this
Code: 
Sub ConcatDupes()

   Dim Cl As Range
   Dim Itm As Variant
   Dim OrigWs As Worksheet
   Dim DestWs As Worksheet
   
   Set OrigWs = Sheets("Records")
   Set DestWs = Sheets("Sheet3")
Application.ScreenUpdating = False
   With CreateObject("scripting.dictionary")
      For Each Cl In OrigWs.Range("A2", OrigWs.Range("A" & Rows.Count).End(xlUp))
         If Not .exists(Cl.Value) Then
            .Add Cl.Value, Array(Cl, Cl.Offset(, 4).Value)
         Else
            .Item(Cl.Value) = Array(.Item(Cl.Value)(0), .Item(Cl.Value)(1) &[COLOR=#ff0000] ", "[/COLOR] & Cl.Offset(, 4).Value)
         End If
      Next Cl
      For Each Itm In .items
         DestWs.Range("A" & Rows.Count).End(xlUp).Offset(1).Resize(, 4).Value = Itm(0).Resize(, 4).Value
         DestWs.Range("E" & Rows.Count).End(xlUp).Offset(1).Value = Itm(1)
         DestWs.Rows(1).Value = OrigWs.Rows(1).Value
      Next Itm
   End With
End Sub
Click to expand...


it works great pal but ive found it deletes some titles at the top of page see bleow, % space and area get wiped ? and also it duplicates info at the bottom of the page ? brings 'area' 'customer' and 'address' over??

[TABLE="width: 2175"]
<colgroup><col width="64"><col width="236"><col width="265"><col width="582"><col width="178"><col width="58"><col width="62" span="8"><col width="78"><col width="78"><col width="76"><col width="64"></colgroup><tbody>[TR]
[TD="class: xl63, width: 64"]ID
[/TD]
[TD="class: xl63, width: 236"]AREA
[/TD]
[TD="class: xl63, width: 265"]CUSTOMER
[/TD]
[TD="class: xl63, width: 582"]ADDRESS
[/TD]
[TD="class: xl63, width: 178"]W/O NUMBER
[/TD]
[TD="class: xl63, width: 58"]INSTRUCTIONS[/TD]
[TD="class: xl63, width: 62"]DSEN[/TD]
[TD="class: xl63, width: 62"]DTOR[/TD]
[TD="class: xl63, width: 62"]DALLER[/TD]
[TD="class: xl63, width: 62"]SSEN[/TD]
[TD="class: xl63, width: 62"]STOR[/TD]
[TD="class: xl63, width: 62"]SALLER[/TD]
[TD="class: xl63, width: 62"]SPECIALIST[/TD]
[TD="class: xl63, width: 62"]INSTALL[/TD]
[TD="class: xl64, width: 78"]VALUE[/TD]
[TD="class: xl66, width: 78"]% SPACE[/TD]
[TD="class: xl63, width: 76"]AREA CBM[/TD]
[TD="class: xl63, width: 64"]SEE?[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,223,893
Messages
6,175,244
Members
452,622
Latest member
Laura_PinksBTHFT

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top