Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
combine rows and sum data with the same id | Excel Discussion (Misc queries) | |||
How to combine data based on two fields.. | Excel Discussion (Misc queries) | |||
combine 2 tables of data into one based on date | Excel Worksheet Functions | |||
Combine data rows in Pie Chart | Charts and Charting in Excel | |||
Macro to combine data based on IF,THENs? | Excel Programming |