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:

Before:
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.

On Jun 19, 9:21 am, "mojocojo2...@gmail.com" <mojocojo2...@gmail.com>
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:
>
> Before:
> 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

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 (A17 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$22,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).



"(E-Mail Removed)" wrote:
>
> On Jun 19, 9:21 am, "mojocojo2...@gmail.com" <mojocojo2...@gmail.com>
> 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:
> >
> > Before:
> > 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

On Jun 19, 1:03 pm, Dave Peterson <peter...@verizonXSPAM.net> 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 (A17 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$22,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).
>
>
>
>
>
> "mojocojo2...@gmail.com" wrote:
>
> > On Jun 19, 9:21 am, "mojocojo2...@gmail.com" <mojocojo2...@gmail.com>
> > 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:
>
> > > Before:
> > > 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.

On Jun 19, 2:50 pm, "mojocojo2...@gmail.com" <mojocojo2...@gmail.com>
wrote:
> On Jun 19, 1:03 pm, Dave Peterson <peter...@verizonXSPAM.net> 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 (A17 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$22,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).
>
> > "mojocojo2...@gmail.com" wrote:
>
> > > On Jun 19, 9:21 am, "mojocojo2...@gmail.com" <mojocojo2...@gmail.com>
> > > 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:
>
> > > > Before:
> > > > 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

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.




"(E-Mail Removed)" 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