Formula or VBA - Comparison CSV Values between 3 cells for used range. Find unique values.

Dang, that was not easy. There has to be a better way to do this, so if any of you gurus want to share it, I'm interested. However, this should work:

Please Login or Register  to view this content.

Yes, this will compare all data except in column A and populate the next available column for each row. If you want it to always populate column E, it can, but I wrote it in case you had more than 3 lists to compare. The only assumption I made is that numbers are not repeated within a single list. If that's not true, I can modify the code to check for duplicates inside the same list too.

I just realized a better solution. This is more concise, and can deal with duplicates within a single list.

Please Login or Register  to view this content.

In terms of processing speed, I think that it could be made faster in theory, if I only tested items that I hadn't yet encountered, but unless you are dealing with huge data sets, there won't be any noticeable difference.

I am sorry for being unclear, I kind of need two separate similar codes:

1st one - unique between columns from columns B as above (flexible populate last column - adjacent to the part in column A).
2nd one - column B is base values (identify values found in adjacent column C and/or D BUT NOT FOUND FOR THE ADJACENT record in column B) populate in last column as above (flexible)

Sorry if this is all too complicated.

Thankyou,

Can you post examples of what you want the results to be for each?

Output 1: I think it works for 3 columns(not 2 or 4 flexible) but only as far as line 198 when it says out of range error message. If I copy down more values the out of range error message appears?

Does anyone have any ideas for output 2?

Thankyou Gents.

This produces output 2 for me. Again, there might be a prettier way to do this, but it works (at least on PC).

Please Login or Register  to view this content.

Originally Posted by grphillips

Output 1: I think it works for 3 columns(not 2 or 4 flexible) but only as far as line 198 when it says out of range error message. If I copy down more values the out of range error message appears?

Does anyone have any ideas for output 2?

Thankyou Gents.

1. "I think it works for 3 columns" You think? Does it or doesn't it?
2. "not 2 or 4 flexible". You only asked for 3 columns in Post#1 so that's all the code did. Flexibility is easy if you'd like to be more specific
3. There's nothing in that code which should cause anything untoward to happen at line 198. Without seeing your data it's hard to say more. I copied your Post#1 data down 1000 rows, ran the code, and it all worked fine for me.
4. Are any of your numbers greater than 500 or less than 100? If so, are you able to specify their feasible range?

Anyway, here's a modified code. "Flexibility" as indicated in red near the top.
Run it on the same dataset that gave you the 198th row error.
Does it still give the same error, or does it just give blanks (after row 197 or 198) in the column in which you want the output?

Please Login or Register  to view this content.

I've posted 3 solutions, AB33 has posted 2, and kalak has posted 2. If it still doesn't work it's time to 1. Try running it on a pc, since that's where it will be run and 2. post a more robust sample that looks like your actual data.

Here is the first example you gave, with both my solutions. Notice that both produce the desired output. I also attached output 2 with my solution. Notice that it also produces the desired output.

I'm sorry, I have no experience with Macs. Let me know how it works on a PC.

Over 200 views thats pretty popular for a thread!

Question: Why does Output 1 have 3 sets of code within – Sub Find_Unique and Sub_Find Unique 2 and then a function, what’s the difference?

Output 2 has 1 Sub only but no function?


Output 1

Sub find_unique()
Dim arr() As Variant
Dim uniq As String
col = WorksheetFunction.CountA(Range("2:2")) - 2
ReDim arr(0 To col)
For a = 2 To WorksheetFunction.CountA(Range("A:A"))
For x = 0 To col
arr(x) = Split(Cells(a, x + 2).Value, ";")
Next x
For x = 0 To col
For i = 0 To UBound(arr(x))
isuniq = True
For y = 0 To col
If y <> x Then isuniq = isuniq And (Not UBound(Filter(arr(y), arr(x)(i))) > -1)
Next y
If isuniq Then uniq = uniq & ";" & arr(x)(i)
Next i
Next x
Cells(a, col + 3).Value = uniq
uniq = ""
Next a

End Sub


Sub find_unique2()
Dim list$, uniq() As String, arr() As String
Dim a&, x&, y&, col&, r As Long
col = Sheets(1).UsedRange.Columns.Count
r = WorksheetFunction.CountA(Range("A:A"))

ReDim uniq(2 To r)
For a = 2 To r
list = ""
For x = 2 To col
list = list & ";" & Cells(a, x).Value
Next x
arr = Split(list, ";")
For y = 1 To UBound(arr)
If UBound(Filter(arr, arr(y))) = 0 Then uniq(a) = uniq(a) & ";" & arr(y)
Next y
Next a
Cells(2, col + 1).Resize(r - 1, 1).Value = uniq

End Sub

Function IsInArray(stringToBeFound As String, arr As Variant) As Boolean
IsInArray = (UBound(Filter(arr, stringToBeFound)) > -1)
End Function


Output 2:

Sub find_unique()
Dim arr() As Variant
Dim uniq() As String
col = WorksheetFunction.CountA(Range("2:2")) - 2
ReDim arr(0 To col)

For a = 2 To WorksheetFunction.CountA(Range("A:A"))
j = 0
For x = 0 To col
arr(x) = Split(Cells(a, x + 2).Value, ";")
c = c + UBound(arr(x)) + 1
Next x
ReDim uniq(0 To c - 1)
For x = 1 To col
For i = 0 To UBound(arr(x))
isuniq = True
If (Not UBound(Filter(arr(0), arr(x)(i))) > -1) And Not UBound(Filter(uniq, arr(x)(i))) > -1 Then
uniq(j) = arr(x)(i)
j = j + 1
End If
Next i
Next x
ReDim Preserve uniq(0 To j)
Cells(a, col + 3).Value = Join(uniq, ";")

Next a

End Sub

Find unique 1 and find unique 2 are both solutions to your initial sample. They basically do the same thing, but the second one is a bit prettier from a coding standpoint. Use whichever you like best. The function is unnecessary. I was using it in a draft of my answer and then ended up taking it out. You can delete it. For output 2, find_unique should produce the same result you gave.
Note that Output 1 is different than the first sample you gave. My first two functions work on the first example and may or may not work on output 1.

The desired results for Output 2 is different though from output 1?

The coding should be different?

Yes. You have posted 3 files:
1. CSV unique values between 3 cells.xlsx‎
2. Output1.xlsx
3. Output2.xlsx

My 1st 2 solutions were designed for #1 and my third solution was for #3.

No. They work perfectly fine on my PC, just as they are. The function is not necessary at all.

My first and third solutions are set to work on the active sheet. My 2nd solution has a place in it where it says Sheets(1). You can change this to ActiveSheet. Just insert a new module and put the code in there and it should work.

Yay!! this worked. 1 of the most viewed threads on the forum - excellent almost common use - excellent usability by others. Has to be 5 star!

I will keep any eye out as I do more tests especially on large amounts of data.

I did a little testing on small amounts of dat and this seems fine.


Thanks all very much,

You're welcome. Glad I could help Let us know if you need anything else, and +Rep the people who helped you out.