Finding duplicate data and merging cells

Hello all. I'm going to be incredibly selfish, as this is my first post in this forum and it will involve me asking for help. However, time is of the essence for me, and so I don't have much choice.

What I have is a massive table of data (15k+ entries), which consists of 4 different tables merged together. I have a lot of duplicate entries. However, most of these entries have information in one or another columns that its duplicate does not.

What I need to do is:

Identify the duplicate entries (by part number, which is one of the columns).

Merge the duplicate entries. In the merging process, I need to carry information over, taking data that is currently in 2 or more rows and merging it into one row.

Here's an example:

column1 column2 column3 column4
row1 a . . d
row2 . b
row3 . . c

needs to turn into:

column1 column2 column3 column4
row1 a b c d


If I'm not explaining this well, I will do my best to clarify. My main question is, is there any way to automate this process (even partially)? Any help would be greatly appreciated.

Thanks.

I'm going to be incredibly selfish, this is my first post in this forum and it will involve me asking for help

A help forum where no one asked for help would be a Zen experience.

Yes, it's possible to automate. What happens when overlapping data exists in both records?

With overlapping data, it would be best if I could use the data from the first column found with the relevant part number. So, for instance, if row1 was part number x, with description xyz, and row 2 was part number x, with description abc, it would work best if the description for the merged column became xyz.

The reason is because there are some duplicate part numbers that are actually for different parts. I'm combining tables with the part numbers and information from a bunch of different manufacturers, and some of those manufacturers use the same part number. Losing that information in the merging process is something I'm willing to deal with, as I have to have this thing done by January 1st.

there are some duplicate part numbers that are actually for different parts. I'm combining tables with the part numbers and information from a bunch of different manufacturers, and some of those manufacturers use the same part number.

Right, but if the manufacturer is in each record, then the combination of mfr and part number are unique (right?).

Can you post a representative sample of data -- enough that it captures all the possibilities?

Sure thing.

Edit: parsing into this forum isn't working, so I'll try attaching the spreadsheet.

Hope this works...

A spreadsheet -- zip and attach.

Again, hope this works...

I was just wondering if anyone thought they might be able to help with this. I've started merging them manually, and it's about to make me tear my hair out :/

1. What rule should be applied to decide if two part numbers are the same; e.g.,

is 00007 (bearing) the same as 007 (1/2" blank)?
is 00029 (yoke) the same as 0029 (motor yoke)?

2. What determines which data should be used when there is different info in the same columns for the same part?

The part numbers should be exactly the same. So in the two examples you posted, they should have seperate listings. Unfortunately, the 2nd example you listed is actually the same part, but it's listed under the wrong part number in one of our locations, and therefore it's acting as a second part. So the greater good is served by only merging part numbers that are exactly the same.

One possible exception I would like to make to that, if it's possible, is to have any -'s or spaces ignored, as there's hundreds of part numbers that are the same, but show up as different because someone wanted to add a -. Periods (.) are a seperate thing from that, as they are used to seperate parts that are different but that have the same part number.

The second question is also going to be a greater good answer as well. The best answer that I can come up with is to always use the information that's in a higher row, since most of those came off a list that I believe to be more accurate than the other three. So really, everything in a higher row should override the other rows, unless the first row doesn't have anything entered in that column.

Does that make sense?

Higher row meaning it appears higher in the table (i.e., has a lower row number), or meaning a higher row number (i.e, appears lower in the table)?

If the higher precedence row is blank in a certain column, and the lower precedence row has a value, what's the output?

Higher in the table, so a lower row number should take precedence.

The only way the row taking precedence (the one higher in the table) should accept data is if it doesn't already have a value for that column. So, to answer your question, if the higher row has nothing in the column, then it takes the value from the lower colum. I'll try to do an example.

Row 1 | value a | value a | no value | value a|
Row 2 | value b | value b | value b | value b|

should, after merging, look like:

Row 1 |value a | value a | value b | value a |

That's a much better explanation, thank you.

I'll take a look at it later today if no one else does so first.

From the cursory inspection I gave the output, this thing is bleeping beautiful! Thanks a ton man, if this works the way it looks like it does, you just saved me from a very hellish 1.5 months!

I was more concerned with the part numbers that are identical other than dashes or spaces.

Such as

C403 0292
C403-0292

These are the same part, with basically the same part number, but not close enough that the merging macro recognizes them and merges them. Periods and S's generally denote different parts, so those should stay.

I feel like a heel asking for anything more, but is it possible to have the macro merge parts that are only different by a dash or a space?

If not, no worries, there's only a few hundred of those, so I can do them manually.

Thanks again for all your help!

Can all spaces and hyphens be deleted? Or all spaces replaced with hyphens?

Try this ...

Hi, Im looking for similiar macro and didnt see the attachment mentioned on the previous page.. thanks

I like it!!