Excel 2010 - Adapt formulas to find duplicate and unique values with alphanumeric data

You can use this to separate the values into columns C:Q

=IFERROR(TRIM(MID(SUBSTITUTE($A1,", ",REPT(" ",LEN($A1))),(COLUMN(A$1)-1)*LEN($A1)+1,LEN($A1))),"")

Not sure how that's going to work with the SMALL function. Maybe convert and concatenate the CHAR code for each letter to a number.

For example:

Formula:

Please Login or Register  to view this content.

will convert BG12 to 667112.

So maybe another set of helper columns will be required.

I have run out of time for a while so ended up making the below quite crude, the code below was made to work with the data you have in 'Sheet2':

Please Login or Register  to view this content.

You're welcome. Thanks for the rep.



If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon (Next to Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

I think it's an automated reply you have there, right?

Yep. It's actually two standard messages. The first to thank you for the rep, so thanks again. And the other that asks you to mark the thread as solved, assuming that it is. The second part of that message is to encourage OPs to consider all contributors regardless of how effective the suggestion/solution was. If you've already done it, that's great; if not, it's a prompt to do it. If I'd seen a reply from CheeseSandwich, I would probably have removed the second part. Hope that helps.

I always give rep to every one, no effort goes unnoticed with me.

Thank you for that. You would be surprised how many people don't give rep, or even say thank you ... and sometimes disappear, never to be heard from again. And that can be after a lot of time and effort.

Rep isn't everything but it is a measure of your effectiveness, and it is very much appreciated.

but he could be asleep at the moment and has not seen it.

That would be early to bed, but not impossible. It's only 8:15 pm here.

I am still awake... Just been on dad duty.

Thanks for the rep.

I might get time to refine the code tomorrow, unless the VBA solution is not really needed?

"Dad duty"... oh those days. Soon it will be the "Grandpa duty" days. Time passes way too fast. BUT IT'S AWESOME. It's what life is about... that and chess, and soccer, and pizza, and coding.


The real sheet has other formulas that point to the range C:Q and it does other computation with the extracted data. There is no extracted data to the C:Q range when using the VBA option... but it does exactly what I need on its own.

If the VBA code you wrote can be made to fit directly onto the Sheet1 (on the existing layout structure), say columns A and B to be used as they are now in the code you wrote (not touching the C:Q range), and then column R to label "Duplicate set x" and columns V and W starting at row 22 and below for the count which and how many repeat, that would be awesome. Then I will use the formula provided by TMS to extract the data from A onto C:Q and since the VBA will not touch that range, then all will be good, perfect actually.

Thanks you so much for helping me.

I noticed that you have used '@INDEX' in one of your formulas. What version of Excel are you using this in (What version does it need to work in)?

You've posted in the formulas section, but now seem to want VBA - shall I move the thread for you?

It depends... if the VBA gets adjusted to my existing layout, then it's a winner. Or if the formulas get to work, then that is that. I honestly think that I will use both. The TMS formula solves half my problem (it extracts the alphanumeric data in one cell into multiple cells), and the VBA does the other half where it finds the duplicate and the unique values and it counts it. Right now I'm trying to modify the VBA so that the results go into specific columns but I'm stuck... I can't move column C.

Also, I did posted in the formula section because originally I was trying to adjust the existing formulas I have, but it may be more troublesome...

Originally Posted by Luisftv

I can't move column C

With the above in mind, does this mean you can't sort the range C:Q?

Maybe the below layout will be better?

Please Login or Register  to view this content.

Wow... yes. I only need the following modifications. I've been trying to do it but I make a mess each time.

• The data must start at row 1... no headers in the rage A:W
• Move the "Sorted and joined" output column R to column B
• Move the "Duplicate" output column (where you read "Unique" or "Duplicate Set x") from column S to column R
• Column S must be empty. That column is being used for a different purpose already
• Range C:Q is where the data gets spitted onto - and it can be sorted or not (sorted is better)
• The output in Z and AA is perfect there (I love the headers you put there)
• I also love the Drop-Down box you placed in V3, but since there are no headers in that column either, I has to be in V2 (jajaja that I was able to move)

Like this:

01.png
02.png
03.png


Please see attached workbook. I simply copy/pasted the data to make it look as it is desired. I am still trying to modify the VBA myself (the attached workbook here has your code untouched by me). The "Offset(, 2)" (for example) is what I still don't quite understand.


This is as far as I got, and I hope I'm not so far off (I still need to make your awesome drop down box work), and I'm not sure about the part in red, maybe I got it right, but the pull down on V2 does not highlight anything (the one you made):

Please Login or Register  to view this content.

I think I got it.

I had forgotten to view/change the cell references in column W.

Now everything works as needed. It's awesome. Super awesome. Everything works, the pull down you added, the highlighting, etc.

Can you please confirm that the changes I made to the VBA code are correct? I don't want to loose data inadvertently.

I'm attaching the workbook here too.

Here is the code with my tiny modifications (just so that the output would be moved to the needed columns). The blue color indicates where I have doubts of what I did. That "Z2:Z" is followed on the next line by "Z1:Z". I don't understand yet why that second line has to be Z1, otherwise it makes a duplicate in the Z column. But everything seems to work as expected though.

Please Login or Register  to view this content.

I don't know if this is the correct way of doing it, but is working:

Please Login or Register  to view this content.

With that, now it leaves alone the first row on columns Z and AA. This solution probably is really crude.

Try:

Please Login or Register  to view this content.

CheeseSandwich,


It works flawlessly. Thank you so much.

I had forgotten to add a "ClearContents" to the "Paste from Clipboard" sub (which is unrelated to your VBA solution). So, I'm attaching the final working file for reference (and includes what I had forgotten).

Have a wonderful day and THANK YOU once again.

You are very welcome, glad you have what you need.