Solved

Analyse Columns and Summarise them by assigning numeric codes

Posted on 2012-04-06
6
348 Views
Last Modified: 2012-04-10
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.


0
Comment
Question by:ceneiqe
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 26

Expert Comment

by:redmondb
ID: 37817585
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)

Open in new window


Regards,
Brian.Analyse-Columns-V2.xls
0
 

Author Comment

by:ceneiqe
ID: 37818454
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 Comment

by:ceneiqe
ID: 37818459
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
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

 
LVL 26

Accepted Solution

by:
redmondb earned 120 total points
ID: 37821335
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

Open in new window

Regards,
Brian.Analyse-Columns-V3.xls
0
 

Author Closing Comment

by:ceneiqe
ID: 37827913
Thanks for being prompt.
0
 
LVL 26

Expert Comment

by:redmondb
ID: 37829062
Thanks, ceneiqe.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

710 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question