Consolidating unique values from a list

[PatrickW]

Hi, all

I have a list of Job Titles in column A of a spreadsheet, and a corresponding set of Occupational Levels in column B. e.g.

A:            B:
Director      Top Management
HR Manager    Senior Management
Director      Senior Management
Director      Senior Management
Director      Top Management

etc.

As can be seen, though, a given Job Title can fall under a variety of Occupational Levels.

Using the formulae of Aladin and others found here, I've managed to get a unique list of each level, but only if the Job Title-Occupational Level combination is unique.

I need to get a consolidated list of every unique Job Title in every Occupational Level, like:

Top Management   Senior Management
Director         HR Manager
                 Director

etc.

How would I go about doing this?

I look forward to your answers.

PatrickW

 

[PatrickW]

Any help for this one?

 

[MickG]

Hi, Try this:-
Results start "F1".

[COLOR="Navy"]Sub[/COLOR] MG16Apr56
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] RayB, Ray
[COLOR="Navy"]Dim[/COLOR] Rng2 [COLOR="Navy"]As[/COLOR] Range, Dn2 [COLOR="Navy"]As[/COLOR] Range, B, oMax [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("B1"), Range("b" & Rows.count).End(xlUp))
ReDim Ray(1 To Rng.count, 1 To Rng.count)
n = 1
[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
    .CompareMode = vbTextCompare
        [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
            [COLOR="Navy"]If[/COLOR] Not .Exists(Dn.Value) [COLOR="Navy"]Then[/COLOR] .Add Dn.Value, ""
        [COLOR="Navy"]Next[/COLOR]
            RayB = .keys
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
    .CompareMode = vbTextCompare
        [COLOR="Navy"]For[/COLOR] B = 0 To UBound(RayB)
            Ray(1, B + 1) = RayB(B)
                [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn2 [COLOR="Navy"]In[/COLOR] Rng.Offset(, -1)
                    [COLOR="Navy"]If[/COLOR] Dn2.Next.Value = RayB(B) And Not .Exists(RayB(B) & Dn2.Value) [COLOR="Navy"]Then[/COLOR]
                        n = n + 1
                        .Add (RayB(B) & Dn2.Value), n
                        Ray(n, B + 1) = Dn2.Value
                    [COLOR="Navy"]End[/COLOR] If
                [COLOR="Navy"]Next[/COLOR] Dn2
        oMax = Application.Max(n, oMax)
        n = 1
        [COLOR="Navy"]Next[/COLOR] B
[COLOR="Navy"]End[/COLOR] With
Range("f1").Resize(oMax, UBound(RayB) + 1) = Ray
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[PatrickW]

Well hot ****! That worked superbly.

I understand almost nothing of the coding, but the results are exactly what I'm looking for.

There's a problem, though - the data range of the table from which this data is extracted can be up to 10,000 rows long, and when I have that much data (or around 6000+ rows), I get an error message: "Runtime error (7) - Out of memory"

Is there any way of getting around that?

Alternatively, is there a formula-based solution?

Thank you for your comprehensive, prompt response, though!

PatrickW

 

[MickG]

Hi, There was an error in the code , change the 4th line down to the code as below.
NB:- If the number of Headers (columns) produced from your Data is greater than the number of columns on your sheet you will get another error, and you will have to rethink the layout.
I have just run the code, with 10,000 rows that produces a result 420 headers with approx 43 rows (43 x240 = 10080)
Nb:- The code takes about 2 minutes to run.
Could possibly reduce this if I set it all in arrays !!!

ReDim Ray(1 To Rng.count, 1 To Columns.count)

 

[Aladin Akyurek]

...

the data range of the table from which this data is extracted can be up to 10,000 rows long

...

Alternatively, is there a formula-based solution?

...

Yes, it will probably be slower than VBA code or a formula system set up using functions from the morefunc.xll add-in...

 

[PatrickW]

Hi, all

MickG - thanks for your proposed solution, however, I still get the same 'out of memory' error when running the code with between 2000 and 3000 entries.

There will only be a maximum of 6 different entries in column B, and these 6 entries are known. Does this change your solution/analysis?

Aladin - I don't mind it being slower! As it is, the spreadsheet, with all of its functionality, takes around 5 seconds to calculate, and speed isn't critical here - it's just a solution that will work for all entries up to around 10,000 that I require.

Thanks,

PatrickW

 

[Aladin Akyurek]

...
Aladin - I don't mind it being slower! As it is, the spreadsheet, with all of its functionality, takes around 5 seconds to calculate, and speed isn't critical here - it's just a solution that will work for all entries up to around 10,000 that I require.

...

I assume you would want to try...

<TABLE style="WIDTH: 400pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=533 x:str><COLGROUP><COL style="WIDTH: 59pt; mso-width-source: userset; mso-width-alt: 2889" width=79><COL style="WIDTH: 107pt; mso-width-source: userset; mso-width-alt: 5229" width=143><COL style="WIDTH: 21pt; mso-width-source: userset; mso-width-alt: 1024" width=28><COL style="WIDTH: 19pt; mso-width-source: userset; mso-width-alt: 914" width=25><COL style="WIDTH: 90pt; mso-width-source: userset; mso-width-alt: 4388" width=120><COL style="WIDTH: 104pt; mso-width-source: userset; mso-width-alt: 5046" width=138><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 59pt; HEIGHT: 12.75pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 height=17 width=79>Job Title</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 107pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 width=143>Occupational Level</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 21pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 width=28> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 19pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 width=25> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 90pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 width=120 align=right x:num x:fmla='=SUM(IF(FREQUENCY(IF(B2:B6<>"",MATCH(B2:B6,B2:B6,0)),(ROW($A$2:$A$6)-ROW($A$2)+1)),1))' x:arrayrange="E1">2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 104pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 width=138> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl23 height=20>Director</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24>Top Management</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 align=right x:num x:fmla='=SUM(IF(FREQUENCY(IF($A$2:$A$6<>"",IF($B$2:$B$6=E$3,MATCH($A$2:$A$6,$A$2:$A$6,0))),(ROW($A$2:$A$6)-ROW($A$2)+1)),1))' x:arrayrange="E2">1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 align=right x:num x:fmla='=SUM(IF(FREQUENCY(IF($A$2:$A$6<>"",IF($B$2:$B$6=F$3,MATCH($A$2:$A$6,$A$2:$A$6,0))),(ROW($A$2:$A$6)-ROW($A$2)+1)),1))' x:arrayrange="F2">2</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl23 height=20>HR Manager</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24>Senior Management</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 x:fmla='=IF(COLUMNS($E$3:E3)<=$E$1,INDEX($B$2:$B$6,SMALL(IF(FREQUENCY(IF($B$2:$B$6<>"",MATCH("~"&$B$2:$B$6&"",$B$2:$B$6&"",0)),ROW($B$2:$B$6)-ROW($B$2)+1),ROW($B$2:$B$6)-ROW($B$2)+1),COLUMNS($E$3:E3))),"")' x:arrayrange="E3">Top Management</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 x:fmla='=IF(COLUMNS($E$3:F3)<=$E$1,INDEX($B$2:$B$6,SMALL(IF(FREQUENCY(IF($B$2:$B$6<>"",MATCH("~"&$B$2:$B$6&"",$B$2:$B$6&"",0)),ROW($B$2:$B$6)-ROW($B$2)+1),ROW($B$2:$B$6)-ROW($B$2)+1),COLUMNS($E$3:F3))),"")' x:arrayrange="F3">Senior Management</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl23 height=20>Director</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24>Senior Management</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 x:fmla='=IF(ROWS(E$4:E4)<=E$2,INDEX($A$2:$A$6,SMALL(IF(FREQUENCY(IF($A$2:$A$6<>"",IF($B$2:$B$6=E$3,MATCH($A$2:$A$6,$A$2:$A$6,0))),(ROW($A$2:$A$6)-ROW($A$2)+1)),(ROW($A$2:$A$6)-ROW($A$2)+1)),ROWS(E$4:E4))),"")' x:arrayrange="E4">Director</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 x:fmla='=IF(ROWS(F$4:F4)<=F$2,INDEX($A$2:$A$6,SMALL(IF(FREQUENCY(IF($A$2:$A$6<>"",IF($B$2:$B$6=F$3,MATCH($A$2:$A$6,$A$2:$A$6,0))),(ROW($A$2:$A$6)-ROW($A$2)+1)),(ROW($A$2:$A$6)-ROW($A$2)+1)),ROWS(F$4:F4))),"")' x:arrayrange="F4">Director</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl23 height=20>Director</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24>Senior Management</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 x:str="" x:fmla='=IF(ROWS(E$4:E5)<=E$2,INDEX($A$2:$A$6,SMALL(IF(FREQUENCY(IF($A$2:$A$6<>"",IF($B$2:$B$6=E$3,MATCH($A$2:$A$6,$A$2:$A$6,0))),(ROW($A$2:$A$6)-ROW($A$2)+1)),(ROW($A$2:$A$6)-ROW($A$2)+1)),ROWS(E$4:E5))),"")' x:arrayrange="E5"> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 x:fmla='=IF(ROWS(F$4:F5)<=F$2,INDEX($A$2:$A$6,SMALL(IF(FREQUENCY(IF($A$2:$A$6<>"",IF($B$2:$B$6=F$3,MATCH($A$2:$A$6,$A$2:$A$6,0))),(ROW($A$2:$A$6)-ROW($A$2)+1)),(ROW($A$2:$A$6)-ROW($A$2)+1)),ROWS(F$4:F5))),"")' x:arrayrange="F5">HR Manager</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 height=20>Director</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24>Top Management</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 x:str="" x:fmla='=IF(ROWS(E$4:E6)<=E$2,INDEX($A$2:$A$6,SMALL(IF(FREQUENCY(IF($A$2:$A$6<>"",IF($B$2:$B$6=E$3,MATCH($A$2:$A$6,$A$2:$A$6,0))),(ROW($A$2:$A$6)-ROW($A$2)+1)),(ROW($A$2:$A$6)-ROW($A$2)+1)),ROWS(E$4:E6))),"")' x:arrayrange="E6"> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 x:str="" x:fmla='=IF(ROWS(F$4:F6)<=F$2,INDEX($A$2:$A$6,SMALL(IF(FREQUENCY(IF($A$2:$A$6<>"",IF($B$2:$B$6=F$3,MATCH($A$2:$A$6,$A$2:$A$6,0))),(ROW($A$2:$A$6)-ROW($A$2)+1)),(ROW($A$2:$A$6)-ROW($A$2)+1)),ROWS(F$4:F6))),"")' x:arrayrange="F6"> </TD></TR></TBODY></TABLE>

A1:B6 (on Sheet1) houses the sample you provided, the headers included.

Define Rvec by means of Insert|Name|Define as referring to:

=ROW(Sheet1!$A$2:$A$6)-ROW(Sheet1!$A$2)+1

In what follows, all special char features from the formulas are removed for it's expected that there aren't any special chars like <, *, etc. around the entries.

E1, control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(B2:B6<>"",MATCH(B2:B6,B2:B6,0)),Rvec),1))

E2, control+shift+enter and copy across:

=SUM(IF(FREQUENCY(IF($A$2:$A$6<>"",IF($B$2:$B$6=E$3,
    MATCH($A$2:$A$6,$A$2:$A$6,0))),Rvec),1))

E4, control+shift+enter, copy across, and then down:

=IF(ROWS(E$4:E4)<=E$2,INDEX($A$2:$A$6,
   SMALL(IF(FREQUENCY(IF($A$2:$A$6<>"",IF($B$2:$B$6=E$3,
     MATCH($A$2:$A$6,$A$2:$A$6,0))),Rvec),Rvec),
      ROWS(E$4:E4))),"")

 

[PatrickW]

Hi, Aladin

Thanks - I had a formula-based solution that was working only when the Job Title was specific to an Occupational Level, but your solution works perfectly.

It gives the exact output I require, and there are no special characters around the input range, either, so that won't be problematic.

The additional computation time is not that significant, even when the formula is copied down 500-odd rows.

Thanks for providing an prompt, concise and (very) elegant solution. I really appreciate it.

PatrickW