# Analyse Columns and Summarise them by assigning numeric codes

To analyse the columns where object names can appear in any column BUT they DO NOT repeat in the same column.
if the object name appear once = 1
if repeat twice = 2
if repeat thrice = 3

NOTE:
1. Column  A B C E - in text format, F - in numeric format
2. Object name can appear more than once (but of course less than or equal to 3 times) in different COLUMN each.
3. Object name will NOT be repeated in the same column.
4. Object name, if repeated in each column may appear in different rows.

VLOOKUP works in sequence but the search for my case is all jumbled.

###### Who is Participating?

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:
Hi, ceneiqe.

I'm not sure I've correctly understood your requirements as I came up with slightly different results (highlighted) to yours. The formula (for the first row)...
``````=SUM(COUNTIF(A:A,E2)>0,COUNTIF(B:B,E2)>0,COUNTIF(C:C,E2)>0)
``````

Regards,
Brian.Analyse-Columns-V2.xls
0
Author Commented:
your formula looks right. for example ABC1 appear 3 times in 3 diff column thus the code is 3. i put 1 as i counted wrongly. the right result =3 which you got it, same concept for those you have highlighted in red.

but let me run through with the actual data again, thks.
0
Author Commented:
for column E,  it is manually type out, is it possible to summarise column E via macro and then get the results in F?
0
Commented:
ceneiqe,

Is the attached what you're looking for? The macro is...
``````Option Explicit

Sub GetUniqueSets()
Dim xCell As Range
Dim xCollUnique As Collection
Dim i As Long
Dim xLastRow As Long

Sheets("Sheet1").Activate
xLastRow = ActiveSheet.Range("A1").SpecialCells(xlLastCell).Row 'Lazy way to determine no. of rows of sets. - could result in _lots_ of empty cells being checked.
If xLastRow < 2 Then
MsgBox ("No data for sets found. Run cancelled.")
Exit Sub
End If

Range("E2:F" & xLastRow).ClearContents 'in case there's old data.

Set xCollUnique = New Collection

'Build a collection of unique non-blank values...
For Each xCell In Range("A2:C" & xLastRow)
On Error Resume Next
If CStr(xCell.Value) <> "" Then _
xCollUnique.Add xCell.Value, CStr(xCell.Value)  'collection is indexed so if a value already exists then it won't be added.
On Error GoTo 0
Next xCell

If xCollUnique.Count = 0 Then
MsgBox ("No non-blank data for sets found. Run cancelled.")
Exit Sub
End If

' Output unique values...
For i = 1 To xCollUnique.Count
Cells(i + 1, 5).Value = xCollUnique(i)
Next i

'Sort unique values...
Range("E2:E" & xCollUnique.Count + 1).Sort Range("E2"), xlAscending, , , , , , xlNo

' Calculate no. of entries for each unique value...
With Range("F2")
.Formula = "=SUM(COUNTIF(A:A,E2)>0,COUNTIF(B:B,E2)>0,COUNTIF(C:C,E2)>0)"
.Copy Destination:=Range("F2:F" & xCollUnique.Count + 1)
End With
With Range("F2:F" & xCollUnique.Count + 1)
.Copy
.PasteSpecial Paste:=xlValues
End With

Range("A2").Activate
Application.CutCopyMode = False

End Sub
``````
Regards,
Brian.Analyse-Columns-V3.xls
0

Experts Exchange Solution brought to you by