Duplicate Value VBA problem

[danderson1]

Hi everyone,

I am looking for some help with a formula/macro. In the attached file, i have written a short explanation about what I’m trying to achieve, however, it is also listed below.

In essence, I am trying to create a formula/macro that can tell when there is a duplicate value in column A, if there is a duplicate value in column A, it will return the value in Column A & Column B and so on.

For example, for cells A29&A30, they are duplicate values up until column D when you split text to columns, I need the formula to recognise this and return the words until we are left with a unique name.

With the unique name column (D) this is where I want the unique name to be placed, so that by the end we should be left with a list of completely unique names, for example, column D should read something like this:

Aerocrine – because it is unique, but for aluminium Bahrain, it will return both the values in cells A155&156.

I will be helpful for any help you can provide,

Many thanks

Excel Help 1.xls

[Richard Buttrey]

Hi,

I don't understand the rules. You say A29 A30 are duplicates, presumably because up to the - the characters are the same and only want one value returned. Yet seem to be drawing a distinction between these two and A155 & A156, which again are the same characters up to the - but you want both values.

Can you clarify. It would also help considerably if you would manually add the results you expect in column D for an example of all permutations that you have. That way we know what the end goal is.

[AB33]

Duplicate post

http://www.excelforum.com/excel-prog...acro-help.html

[danderson1]

The problem is duplicate names. Ultimately I want to vlookup the unique identifier and S/U. Acron is a good example; these are duplicates, but D, S is also duplicate so the vlookup on the first name is fine. However, all of the China Resources…. Have different unique identifiers and S/U, so in this case I would want the vlookup to find a difference in the name (3rd or 4th word, and then vlookup the unique identifier and s/u)


Thanks for your help!

[danderson1]

Sorry im a new user & couldnt find out how to change the thread name.

[danderson1]

Excel Help 1.xls

Hi,

I don't understand the rules. You say A29 A30 are duplicates, presumably because up to the - the characters are the same and only want one value returned. Yet seem to be drawing a distinction between these two and A155 & A156, which again are the same characters up to the - but you want both values.

Can you clarify. It would also help considerably if you would manually add the results you expect in column D for an example of all permutations that you have. That way we know what the end goal is.

Hi Mr. Buttrey,

i have attached the file again with an example under the chinese names, just to give an idea of what im trying to get at.

thanks again!

[Richard Buttrey]

Hi,

That doesn't really help.

What are the rules for truncating those 'China' names? i.e. why does China Nepstar Chain Drugstore truncate to China Nepstar, but China Oilfield Services
only drops one word and truncates to China Oilfield.

And why is say China Oriental Group truncated to China Oriental but China Railway Construction isn't truncated?

[danderson1]

China Nepstar is truncated to that length because:
1. The word china has many duplicates in column A
2. From looking at the word china & recognising it has duplicates, the formula will look to the second word to see if that also has duplicates, and when China Nepstar is taken as 1, then there are no other duplicate's in Column A, so the word 'China Nepstar' is returned in column D.

Im trying to return the least words possible, but still maintain the uniqueness of the word. so for China Oilfield Services, there is no other name called 'China Oilfield' so it recognises this and only returns the words 'china Oilfield'.

Does this help at all?

thanks

[danderson1]

any ideas guy's? would really appreciate any help!

[danderson1]

surely someone must have an idea?

[Richard Buttrey]

Patience pays off. We don't like being nagged.

See if the attached helps. It uses 3 helper columns F:H and a results column I. As set up it works down to a level of 4 words. If you think there may be more which are significant then you'll need to extend the logic and add more helper columns, and of course adapt the results formula.
I've left an 'auditing' version of the results formula in I1 which is slightly different to the rest of column N formulae in that it identifies which level of the IF statement is in operation. You won't need to use this unless you add another level of analysis, and in which case it will help in modifying the formula.