Combine data in rows based on two citeria
 

I've have not yet come across a very good solution to this. Any help
would be very much appreciated.

I need a macro that is able to iterate through the rows of my data and
then for each row it iterates through takes two pieces of data from
columns of that row (eg: Company and City). It then finds other rows
containing the same data for both Company and City and combines the
data of these rows into only one row.

Here's a representation of my problem:

Befo
After:

Company: City: Address: -------
Company: City: Address:
Company1 City1 444 Broadway
Company1 City1 444 Broadway
Company2 City2
Company2 City2 333 Milburry
Company2 City2 333 Milburry
Company3 City3 111 West
Company1 City1
Company1 City4 666 East
Company3 City3 111 West
Company1 City4 666 East

Thanks in advance to anyone that helps.

Combine data in rows based on two citeria
 

On Jun 19, 9:21 am, "
wrote:
I've have not yet come across a very good solution to this. Any help
would be very much appreciated.

I need a macro that is able to iterate through the rows of my data and
then for each row it iterates through takes two pieces of data from
columns of that row (eg: Company and City). It then finds other rows
containing the same data for both Company and City and combines the
data of these rows into only one row.

Here's a representation of my problem:

Befo
After:

Company: City: Address: -------
Company: City: Address:
Company1 City1 444 Broadway
Company1 City1 444 Broadway
Company2 City2
Company2 City2 333 Milburry
Company2 City2 333 Milburry
Company3 City3 111 West
Company1 City1
Company1 City4 666 East
Company3 City3 111 West
Company1 City4 666 East

Thanks in advance to anyone that helps.

Here's a better representation (it got messed up the first time I
typed it):

Company: City: Address:
Company1 City1 444 Broadway
Company2 City2
Company2 City2 333 Milburry
Company1 City1
Company3 City3 111 West
Company1 City4 666 East


After:

Company: City: Address:
Company1 City1 444 Broadway
Company2 City2 333 Milburry
Company3 City3 111 West
Company1 City4 666 East

Combine data in rows based on two citeria
 

It looks like you're keeping the first row that has the information on it.

How about this?

With your data in A2:C7 (headers in row 1)
I added a formula in D2: =a2&"..."&b2
and dragged down.

Then I selected the range (A1:D7 for me) and sorted by:
Column D in ascending order
column C in descending order
(with headers checked)

Then I added another formula in E2: =CountIf(D$2:D2,D2)
and dragged down
This ended up with 1's in the first entry for that company/city combination.

I filtered to show the values greater than 1 and deleted those visible rows.

Then I deleted my helper columns (D:E).



" wrote:

On Jun 19, 9:21 am, "
wrote:
I've have not yet come across a very good solution to this. Any help
would be very much appreciated.

I need a macro that is able to iterate through the rows of my data and
then for each row it iterates through takes two pieces of data from
columns of that row (eg: Company and City). It then finds other rows
containing the same data for both Company and City and combines the
data of these rows into only one row.

Here's a representation of my problem:

Befo
After:

Company: City: Address: -------
Company: City: Address:
Company1 City1 444 Broadway
Company1 City1 444 Broadway
Company2 City2
Company2 City2 333 Milburry
Company2 City2 333 Milburry
Company3 City3 111 West
Company1 City1
Company1 City4 666 East
Company3 City3 111 West
Company1 City4 666 East

Thanks in advance to anyone that helps.

Here's a better representation (it got messed up the first time I
typed it):

Company: City: Address:
Company1 City1 444 Broadway
Company2 City2
Company2 City2 333 Milburry
Company1 City1
Company3 City3 111 West
Company1 City4 666 East

After:

Company: City: Address:
Company1 City1 444 Broadway
Company2 City2 333 Milburry
Company3 City3 111 West
Company1 City4 666 East

--

Dave Peterson

Combine data in rows based on two citeria
 

On Jun 19, 1:03 pm, Dave Peterson wrote:
It looks like you're keeping the first row that has the information on it.

How about this?

With your data in A2:C7 (headers in row 1)
I added a formula in D2: =a2&"..."&b2
and dragged down.

Then I selected the range (A1:D7 for me) and sorted by:
Column D in ascending order
column C in descending order
(with headers checked)

Then I added another formula in E2: =CountIf(D$2:D2,D2)
and dragged down
This ended up with 1's in the first entry for that company/city combination.

I filtered to show the values greater than 1 and deleted those visible rows.

Then I deleted my helper columns (D:E).





" wrote:

On Jun 19, 9:21 am, "
wrote:
I've have not yet come across a very good solution to this. Any help
would be very much appreciated.

I need a macro that is able to iterate through the rows of my data and
then for each row it iterates through takes two pieces of data from
columns of that row (eg: Company and City). It then finds other rows
containing the same data for both Company and City and combines the
data of these rows into only one row.

Here's a representation of my problem:

Befo
After:

Company: City: Address: -------
Company: City: Address:
Company1 City1 444 Broadway
Company1 City1 444 Broadway
Company2 City2
Company2 City2 333 Milburry
Company2 City2 333 Milburry
Company3 City3 111 West
Company1 City1
Company1 City4 666 East
Company3 City3 111 West
Company1 City4 666 East

Thanks in advance to anyone that helps.

Here's a better representation (it got messed up the first time I
typed it):

Company: City: Address:
Company1 City1 444 Broadway
Company2 City2
Company2 City2 333 Milburry
Company1 City1
Company3 City3 111 West
Company1 City4 666 East

After:

Company: City: Address:
Company1 City1 444 Broadway
Company2 City2 333 Milburry
Company3 City3 111 West
Company1 City4 666 East

--

Dave Peterson- Hide quoted text -

- Show quoted text -

Thank you very much. This is just what I needed.

Combine data in rows based on two citeria
 

On Jun 19, 2:50 pm, "
wrote:
On Jun 19, 1:03 pm, Dave Peterson wrote:





It looks like you're keeping the first row that has the information on it.

How about this?

With your data in A2:C7 (headers in row 1)
I added a formula in D2: =a2&"..."&b2
and dragged down.

Then I selected the range (A1:D7 for me) and sorted by:
Column D in ascending order
column C in descending order
(with headers checked)

Then I added another formula in E2: =CountIf(D$2:D2,D2)
and dragged down
This ended up with 1's in the first entry for that company/city combination.

I filtered to show the values greater than 1 and deleted those visible rows.

Then I deleted my helper columns (D:E).

" wrote:

On Jun 19, 9:21 am, "
wrote:
I've have not yet come across a very good solution to this. Any help
would be very much appreciated.

I need a macro that is able to iterate through the rows of my data and
then for each row it iterates through takes two pieces of data from
columns of that row (eg: Company and City). It then finds other rows
containing the same data for both Company and City and combines the
data of these rows into only one row.

Here's a representation of my problem:

Befo
After:

Company: City: Address: -------
Company: City: Address:
Company1 City1 444 Broadway
Company1 City1 444 Broadway
Company2 City2
Company2 City2 333 Milburry
Company2 City2 333 Milburry
Company3 City3 111 West
Company1 City1
Company1 City4 666 East
Company3 City3 111 West
Company1 City4 666 East

Thanks in advance to anyone that helps.

Here's a better representation (it got messed up the first time I
typed it):

Company: City: Address:
Company1 City1 444 Broadway
Company2 City2
Company2 City2 333 Milburry
Company1 City1
Company3 City3 111 West
Company1 City4 666 East

After:

Company: City: Address:
Company1 City1 444 Broadway
Company2 City2 333 Milburry
Company3 City3 111 West
Company1 City4 666 East

--

Dave Peterson- Hide quoted text -

- Show quoted text -

Thank you very much. This is just what I needed.- Hide quoted text -

- Show quoted text -

I guess I now I have a follow up question concerning this same topic.
What process could I take to attain a similar result as shown above
with more then three columns? For example.

Company: City: Address: Other Info:
Company1 City1 444 Broadway
Company2 City2 ttyy
Company2 City2 333 Milburry
Company1 City1 aabb
Company3 City3 111 West ccdd
Company1 City4 666 East ddxx

After:

Company: City: Address: Other Info:
Company1 City1 444 Broadway aabb
Company2 City2 333 Milburry ttyy
Company3 City3 111 West ccdd
Company1 City4 666 East ddxx

Combine data in rows based on two citeria
 

Copy your data to two worksheets.
Clean up the first like you did before.
Clean up the second using the same kind of technique--but this time use the
"other info" column as the secondary key (in descending order).

So the first sheet will look like (after I delete the other info column):

---A--- -B--- ---C-------- ------D-------
Company: City: Address: combination
Company1 City1 444 Broadway Company1…City1
Company1 City4 666 East Company1…City4
Company2 City2 333 Milburry Company2…City2
Company3 City3 111 West Company3…City3

The second sheet will look like (after I delete the Address column):

---A--- -B--- ---C-------- ------D-------
Company: City: Other Info: combined
Company1 City1 aabb Company1…City1
Company1 City4 ddxx Company1…City4
Company2 City2 ttyy Company2…City2
Company3 City3 ccdd Company3…City3

Now put this in E2 of sheet1:
=index(sheet2!c:c,match(d2,sheet2!d:d,0))
Then drag down the column

Convert to values (edit|copy, edit|paste special|Values)

And if you have an #n/a's, just do an Edit|Replace to get rid of them.




" wrote:
<<snipped

I guess I now I have a follow up question concerning this same topic.
What process could I take to attain a similar result as shown above
with more then three columns? For example.

Company: City: Address: Other Info:
Company1 City1 444 Broadway
Company2 City2 ttyy
Company2 City2 333 Milburry
Company1 City1 aabb
Company3 City3 111 West ccdd
Company1 City4 666 East ddxx

After:

Company: City: Address: Other Info:
Company1 City1 444 Broadway aabb
Company2 City2 333 Milburry ttyy
Company3 City3 111 West ccdd
Company1 City4 666 East ddxx

--

Dave Peterson