Lookup multiple values ignoring duplicates and blanks

G

Gregorykay

New Member
Joined
May 19, 2011
Messages
3
Hi Everyone,

In worksheet 1 I have Colum A which holds names, and column B which holds phone numbers. In workesheet 2 in column A I have every name listed only once.

In worksheet 1 I have lots of duplicates in column A. Most of the time column B has the same phone number for every column A. But sometimes it is different.

I would need a macro that does a lookup of the phone number for each name in worksheet 2 from worksheet 1. If there are repeats of the same number place that number only once in coloumn B, however, if a different phone number exists for that same name place in column C, if still another exists place in coloumn D etc. (Sometimes duplicate names in worksheet 1 have no numbers beside them too)

For example I would want it to look like this

Worksheet 1

ColumnA-----------ColumnB
Name 1----------------123
Name 1- --------------456
Name 1-- --------------678
Name 1----------------678
Name 2----------------444
Name 2----------------444


Worksheet 2

ColumnA-----------ColumnB-----------ColumnC-----------ColumnD
Name 1----------------123- --------------456-- --------------678
Name 2----------------444




Thanks!
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
Sort by date Sort by votes
I know you requested a macro, but, since nobody's jumped in yet with your preferred solution, maybe you'll consider a formula-based approach.

In your first table, I've included 2 helper columns.

Formulas (copy down):
C2: =REPT(A2 & "@" & B2, COUNTIF($B$2:$B2, B2)=1)
D2: =COUNTIF($C$2:$C2, $A2 & "*") * (C2<>"")

<TABLE style="WIDTH: 222pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=296><COLGROUP><COL style="WIDTH: 30pt; mso-width-source: userset; mso-width-alt: 1462" width=40><COL style="WIDTH: 48pt" span=4 width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #f2f2f2; WIDTH: 30pt; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl74 height=20 width=40></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #f2f2f2; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl74 width=64>A</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #f2f2f2; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl74 width=64>B</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #f2f2f2; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl74 width=64>C</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #f2f2f2; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl74 width=64>D</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #f2f2f2; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl74 height=20>1</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: black; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl71>Name</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: black; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl69>Number</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: black; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl69>Helper1</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: black; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl70>Helper2</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #f2f2f2; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl74 height=20>2</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl72>Name 1</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>123</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>Name 1@123</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl66 align=right>1</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #f2f2f2; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl74 height=20>3</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Verdana; BACKGROUND: silver; COLOR: black; FONT-SIZE: 8pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 700; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl72>Name 1</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext 0.5pt solid; FONT-FAMILY: Verdana; BACKGROUND: silver; COLOR: black; FONT-SIZE: 8pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl65 align=right>456</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext 0.5pt solid; FONT-FAMILY: Verdana; BACKGROUND: silver; COLOR: black; FONT-SIZE: 8pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl65>Name 1@456</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext 0.5pt solid; FONT-FAMILY: Verdana; BACKGROUND: silver; COLOR: black; FONT-SIZE: 8pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 1pt solid; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl66 align=right>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: #f2f2f2; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl74 height=20>4</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl72>Name 1</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>678</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>Name 1@678</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl66 align=right>3</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #f2f2f2; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl74 height=20>5</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Verdana; BACKGROUND: silver; COLOR: black; FONT-SIZE: 8pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 700; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl72>Name 1</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext 0.5pt solid; FONT-FAMILY: Verdana; BACKGROUND: silver; COLOR: black; FONT-SIZE: 8pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl65 align=right>678</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext 0.5pt solid; FONT-FAMILY: Verdana; BACKGROUND: silver; COLOR: black; FONT-SIZE: 8pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl65></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext 0.5pt solid; FONT-FAMILY: Verdana; BACKGROUND: silver; COLOR: black; FONT-SIZE: 8pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 1pt solid; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl66 align=right>0</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #f2f2f2; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl74 height=20>6</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl72>Name 2</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>444</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>Name 2@444</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl66 align=right>1</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #f2f2f2; HEIGHT: 15.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl74 height=21>7</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Verdana; BACKGROUND: silver; COLOR: black; FONT-SIZE: 8pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 700; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl73>Name 2</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 0.5pt solid; FONT-FAMILY: Verdana; BACKGROUND: silver; COLOR: black; FONT-SIZE: 8pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl67 align=right>444</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 0.5pt solid; FONT-FAMILY: Verdana; BACKGROUND: silver; COLOR: black; FONT-SIZE: 8pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl67></TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 0.5pt solid; FONT-FAMILY: Verdana; BACKGROUND: silver; COLOR: black; FONT-SIZE: 8pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 1pt solid; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl68 align=right>0</TD></TR></TBODY></TABLE>

Now, on your 2nd table (for convenience, I have them both on the same worksheet - amend accordingly), I have the following table:

Formulas (Copy to entire table):
G2: =SUMPRODUCT(--($A$2:$A$7=$F2), --($D$2:$D$7=G$1), $B$2:$B$7)

If you're using Excel 2007 or later, you can use:
G2: =SUMIFS($B$2:$B$7, $A$2:$A$7, $F2, $D$2:$D$7, G$1)

<TABLE style="WIDTH: 288pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=384><COLGROUP><COL style="WIDTH: 48pt" span=6 width=64><TBODY><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #f2f2f2; WIDTH: 48pt; HEIGHT: 15.75pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl71 height=21 width=64></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #f2f2f2; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl71 width=64>F</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #f2f2f2; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl71 width=64>G</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #f2f2f2; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl71 width=64>H</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #f2f2f2; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl71 width=64>I</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #f2f2f2; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl71 width=64>J</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #f2f2f2; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl71 height=20>1</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: black; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl72></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: black; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl73>1</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: black; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl73>2</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: black; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl73>3</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: black; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid" class=xl74>4</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #f2f2f2; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl71 height=20>2</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>Name 1</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 align=right>123</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 align=right>456</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 align=right>678</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl68></TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #f2f2f2; HEIGHT: 15.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl71 height=21>3</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; FONT-FAMILY: Verdana; BACKGROUND: silver; COLOR: black; FONT-SIZE: 8pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 700; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl66>Name 2</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 0.5pt solid; FONT-FAMILY: Verdana; BACKGROUND: silver; COLOR: black; FONT-SIZE: 8pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl69 align=right>444</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 0.5pt solid; FONT-FAMILY: Verdana; BACKGROUND: silver; COLOR: black; FONT-SIZE: 8pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl69></TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 0.5pt solid; FONT-FAMILY: Verdana; BACKGROUND: silver; COLOR: black; FONT-SIZE: 8pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl69></TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 0.5pt solid; FONT-FAMILY: Verdana; BACKGROUND: silver; COLOR: black; FONT-SIZE: 8pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 1pt solid; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl70></TD></TR></TBODY></TABLE>

Use a Custom Format to hide zeroes, and you're done.
 
Last edited:
Upvote 0
If you have lots of data, you might find that all those SUMPRODUCTS or SUMIFS formulas slow things down to a crawl.

To avoid this, make your 2nd table a Pivot Table, as follows:

Column Labels: Helper2
Row Labels: Name
Values: Number

Turn off Grand Totals, Filter out 0 from Helper2, format to your heart's content, and you're done.
 
Upvote 0
I have assumed that the data in 'Worksheet 1' is sorted by Name then by Number as your sample seems to be. I've also assumed headings in A1:B1 in Worksheet 1.

Test in a copy of your workbook.

<font face=Courier New><br><br><SPAN style="color:#00007F">Sub</SPAN> PhoneNumbers()<br>    <SPAN style="color:#00007F">Dim</SPAN> a, b<br>    <SPAN style="color:#00007F">Dim</SPAN> LR <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, Mx <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, j <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, k <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <br>    <SPAN style="color:#00007F">With</SPAN> Sheets("Worksheet 1")<br>        LR = .Range("A" & .Rows.Count).End(xlUp).Row<br>        a = .Range("A1:B" & LR).Value<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    <SPAN style="color:#00007F">ReDim</SPAN> b(1 <SPAN style="color:#00007F">To</SPAN> LR, 1 <SPAN style="color:#00007F">To</SPAN> 2)<br>    b(1, 1) = a(1, 1)<br>    Mx = 2<br>    j = 1<br>    <SPAN style="color:#00007F">For</SPAN> i = 2 <SPAN style="color:#00007F">To</SPAN> LR<br>        <SPAN style="color:#00007F">If</SPAN> a(i, 1) = a(i - 1, 1) <SPAN style="color:#00007F">Then</SPAN><br>            <SPAN style="color:#00007F">If</SPAN> a(i, 2) <> a(i - 1, 2) <SPAN style="color:#00007F">Then</SPAN><br>                k = k + 1<br>                <SPAN style="color:#00007F">If</SPAN> k > Mx <SPAN style="color:#00007F">Then</SPAN><br>                    <SPAN style="color:#00007F">ReDim</SPAN> <SPAN style="color:#00007F">Preserve</SPAN> b(1 <SPAN style="color:#00007F">To</SPAN> LR, 1 <SPAN style="color:#00007F">To</SPAN> k)<br>                    Mx = k<br>                    b(1, k) = a(1, 2)<br>                <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>                b(j, k) = a(i, 2)<br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>        <SPAN style="color:#00007F">Else</SPAN><br>            k = 2<br>            j = j + 1<br>            b(j, 1) = a(i, 1)<br>            b(j, 2) = a(i, 2)<br>            b(1, k) = a(1, 2)<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">Next</SPAN> i<br>    <SPAN style="color:#00007F">With</SPAN> Sheets("Worksheet 2").Range("F1").Resize(LR, Mx)<br>        .Value = b<br>        .SpecialCells(xlCellTypeBlanks).Delete Shift:=xlToLeft<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>
 
Last edited:
Upvote 0
Try:-
Code: 
[COLOR="Navy"]Sub[/COLOR] MG21May24
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Twn [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Q
[COLOR="Navy"]Dim[/COLOR] Num [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]Dim[/COLOR] oMax [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]Dim[/COLOR] ray
[COLOR="Navy"]Dim[/COLOR] k
[COLOR="Navy"]Dim[/COLOR] c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]With[/COLOR] Sheets("Sheet1")
   [COLOR="Navy"]Set[/COLOR] Rng = .Range(.Range("A1"), .Range("A" & rows.Count).End(xlUp))
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
    .CompareMode = vbTextCompare
    [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
        Twn = Dn & "/" & Dn(, 2)
        [COLOR="Navy"]If[/COLOR] Not .Exists(Twn) [COLOR="Navy"]Then[/COLOR] .Add (Twn), ""
    [COLOR="Navy"]Next[/COLOR] Dn
ray = .keys
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
    .CompareMode = vbTextCompare
    ReDim nRay(1 To Columns.Count)
    [COLOR="Navy"]For[/COLOR] Num = 0 To UBound(ray)
        [COLOR="Navy"]If[/COLOR] Not .Exists(Split(ray(Num), "/")(0)) [COLOR="Navy"]Then[/COLOR]
            n = n + 1
            nRay(1) = Split(ray(Num), "/")(0): nRay(2) = Split(ray(Num), "/")(1)
            .Add Split(ray(Num), "/")(0), Array(nRay, n, 2)
        [COLOR="Navy"]Else[/COLOR]
            Q = .Item(Split(ray(Num), "/")(0))
            Q(2) = Q(2) + 1
            Q(0)(Q(2)) = Split(ray(Num), "/")(1)
            .Item(Split(ray(Num), "/")(0)) = Q
        [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR]
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] k [COLOR="Navy"]In[/COLOR] .keys
    Sheets("Sheet2").Range("A" & .Item(k)(1)).Resize(, .Item(k)(2) + 1) = .Item(k)(0)
[COLOR="Navy"]Next[/COLOR] k
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

H
  • Solved
Create list of last(family) names when parents have children in more than 1 class
Replies
7
Views
169
Peter_SSs
Peter_SSs
H
  • Question Question
Using XLOOKUP in VBA to lookup value underneath specific header name (columns can change depending on worksheet)
Replies
0
Views
164
Hailee994
H
W
  • Solved
Index and Match on Multiple Columns with Duplicate Values
Replies
4
Views
718
wsr429
W
L
  • Question Question
Macro to correct entries across multiple sheets
Replies
1
Views
205
Lux Aeterna
L
T
  • Solved
Multiple criteria COUNTIFS with distinct values
Replies
4
Views
1K
Fluff
Fluff

Forum statistics

Threads
1,224,597
Messages
6,179,808
Members
452,944
Latest member
2558216095

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top