Need to find if duplicate values exist in a column, concatenate cells and then delete

Hi all,

I hope I'm posting in the right section. I have a problem that I think is quite difficult, but maybe not. I was going to attach the spreadsheet I was working with, but I keep getting an error. I'll type an example at the end of the message.

What I want to do, is search column A for claim numbers that match. When I do have a matching claim number, I want to concatenate the original cells ownership field with the said matching cells ownership field (or move into a column in the same row, I can always concatenate later). Once that is complete, I want to delete the row I took the information out of.

I want to join this data in ArcGIS, but as of right now, it's not a 1-to-1 relationship, so only a relate works. That doesn't help me as I want to display claims by ownership, and this can vary per claim. Company A may have 100% on one claim, and then split another claim 50% with Company B. This causes a double entry on the claim field in this current spreadsheet I have, which requires me to clean it up by making multiple columns of ownership vs. an additional row for shared ownership.

Any help would be greatly appreciated.

Thanks,

Steve

============================

My problem:

Column A Column B
1235555 Company A (50%)
1235555 Company B (50%)
1235556 Company A (100%)
1235557 Company A (33%)
1235557 Company B (33%)
1235557 Company C (33%)

What I would like to see

Column A Column B Column C Column D
1235555 Company A (50%) Company B (50%)
1235556 Company A (100%)
1235557 Company A (33%) Company B (33%) Company C (33%)

Use this macro:

Please Login or Register  to view this content.

=========
How/Where to install the macro:

1. Open up your workbook
2. Get into VB Editor (Press Alt+F11)
3. Insert a new module (Insert > Module)
4. Copy and Paste in your code (given above)
5. Get out of VBA (Press Alt+Q)
6. Save your sheet

The macro is installed and ready to use.

========
Press Alt-F8 and select Consolidate from the macro list.

Without macros, this can be done with a few steps...

First select go to Data|Filter|Advanced Filter.

Make sure the List Range is your column A range only

Click Copy to another location and enter say, $G$1 in the Copy to field

Select Unique Records Only

Click Ok

A list should be generated in column G

Now in C2, enter formula:

=A2&"_"&COUNTIF(A$2:A2,A2) and copy down

In F2, enter formula:

=COUNTIF(A:A,G2) and copy down to coincide with col. G entries.

In H2, enter formula:

=IF(COLUMNS($A$1:A$1)>$F2,"",INDEX($B:$B,MATCH($G2&"_"&COLUMNS($A$1:A$1),$C:$C,0)))

and copy down and then copy across as far as you want..

After complete, copy the whole sheet and go to Edit|PAste Special and select Values.. this gets rid of all formulas.

Now you can delete columns A:F

See attached sample.. I only went to formulas completed...

AFINLEY,
Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.