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

ID
AREACUSTOMERADDRESSNUMBER
1Delivery Area: 01 - Glasgow / Edinburgh1139 - apple
zzzz
893582
1Delivery Area: 01 - Glasgow / Edinburgh1139 - apple
zzzz
895980
1Delivery Area: 01 - Glasgow / Edinburgh1139 - apple
zzzz
896039
1Delivery Area: 01 - Glasgow / Edinburgh1139 - apple
zzzz
897039
1Delivery Area: 01 - Glasgow / Edinburgh1139 - apple
zzzz
897359
1Delivery Area: 01 - Glasgow / Edinburgh1139 - apple
zzzz
897617
1Delivery Area: 01 - Glasgow / Edinburgh1139 - apple
zzzz
897620
1Delivery Area: 01 - Glasgow / Edinburgh1139 - apple
zzzz
897646
1Delivery Area: 01 - Glasgow / Edinburgh1139 - apple
zzzz
897704
1Delivery Area: 01 - Glasgow / Edinburgh1139 - apple
zzzz
897706
1Delivery Area: 01 - Glasgow / Edinburgh1139 - apple
zzzz
897875
1Delivery Area: 01 - Glasgow / Edinburgh1139 - apple
zzzz
896121
2Delivery Area: 01 - Glasgow / Edinburgh1299 - banana
xxxx
895741
2Delivery Area: 01 - Glasgow / Edinburgh1299 - banana
xxxx
895883
2Delivery Area: 01 - Glasgow / Edinburgh1299 - banana
xxxx
896119
2Delivery Area: 01 - Glasgow / Edinburgh1299 - banana
xxxx
896163
3Delivery Area: 01 - Glasgow / Edinburgh2798 - orange
cccc
895962

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>


my data sorted from id using index match

ID
AREACUSTOMERADDRESSNUMBER
1Delivery Area: 01 - Glasgow / Edinburgh1139 - apple
zzzz
2Delivery Area: 01 - Glasgow / Edinburgh1299 - banana
xxxx
3Delivery Area: 01 - Glasgow / Edinburgh2798 - orange
cccc

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>


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

ID
AREACUSTOMERADDRESSNUMBER
1Delivery Area: 01 - Glasgow / Edinburgh1139 - apple
zzzz
893582 895980 896039 897039 897617 897620 897646 897704 897706 897875 896121
2Delivery Area: 01 - Glasgow / Edinburgh
1299 - banana
xxxx
895741 895883 896119 896163
3Delivery Area: 01 - Glasgow / Edinburgh2798 - orange
cccc
 895962

<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>
</tbody>
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
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:

IDAREACUSTOMERADDRESSNUMBER
1Delivery Area: 01 - Glasgow / Edinburgh1139 - applezzzz893582 895980 896039 897039 897617 897620 897646 897704 897706 897875 896121
2Delivery Area: 01 - Glasgow / Edinburgh1299 - bananaxxxx895741 895883 896119 896163
3Delivery Area: 01 - Glasgow / Edinburgh2798 - orangecccc 895962

<tbody>
</tbody>
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??

ID
AREA
CUSTOMER
ADDRESS
W/O NUMBER
INSTRUCTIONSDSENDTORDALLERSSENSTORSALLERSPECIALISTINSTALLVALUE% SPACEAREA CBMSEE?

<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>
</tbody>
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,215,777
Messages
6,126,832
Members
449,343
Latest member
DEWS2031

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