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]

 

[wrighty998]

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

What runs slow?

i have this in the back,

Function Lookup_concat(Search_string As String, _
Search_in_col As Range, Return_val_col As Range)

Dim i As Long
Dim result As String

For i = 1 To Search_in_col.Count
If Search_in_col.Cells(i, 1) = Search_string Then
result = result & " " & Return_val_col.Cells(i, 1).Value
End If
Next

Lookup_concat = Trim(result)

End Function



and use this in the cell,

=Lookup_concat(A2,COPY!A:A,COPY!E:E)

but it runs very slow pulling the data i have 700 lines to do

thanks

 

[Fluff]

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

What row is your header row in?

 

[wrighty998]

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

What row is your header row in?

only Row A has all headers and have coloumns of data to R

 

[davesexcel]

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

`For i = 1 To Search_in_col.Count`
This could be the problem as the loop would be 1,048,576 times for each formula, you would need to count the data in the range, not count the cells.



Here is one version of just counting the data in the range then using that count in your loop.

Function Lookup_concat(Search_string As String, _
                       Search_in_col As Range, Return_val_col As Range)

    Dim i As Long, LstRw As Long
    Dim result As String
    LstRw = Application.WorksheetFunction.CountA(Search_in_col)
    For i = 1 To LstRw
        If Search_in_col.Cells(i, 1) = Search_string Then
            result = result & " " & Return_val_col.Cells(i, 1).Value
        End If
    Next

    Lookup_concat = Trim(result)

End Function

 

[Fluff]

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

only Row A has all headers and have coloumns of data to R

A is a column not a row, rows are numbered from 1 to 1048576. So which row number are your headers in?

 

[wrighty998]

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

A is a column not a row, rows are numbered from 1 to 1048576. So which row number are your headers in?

sorry row 1 only

 

[davesexcel]

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

using counta would reduce the loop
LstRw = Application.WorksheetFunction.CountA(Search_in_col)

It would be better to find the last used row though in that column,as the counta would not be accurate if you had blank cells. I am unsure how to find the last row in your UDF though, if you are referring a different sheet in the function.

 

[Fluff]

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

My code should not be changing any values in data sheet. Do you have any merged cells?

 

[wrighty998]

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

My code should not be changing any values in data sheet. Do you have any merged cells?

no i dont, its not end of the world mate its only 2 cells

but it does copy info over from the other sheet at the bottom ?

 

[Fluff]

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?