# Tricky Situation

#### harpreet123

##### Board Regular
Hi Experts

I have a situatuion where I have below information:

2150 ABC
2160 kjl
2150 mkn
2162 GHI
2160 HIK

and then I have a list of numbers

2150 ABC mkn
2160 kjl HIK
2162

Is there a formula which can return all variables of 2150 from first table
for ex :result I am looking for is mentioned in second table.

Thanks
H

### Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

#### mgirvin

##### Well-known Member
If you have Excel 2007 or earlier, try this:
Excel Workbook
ABCDEFGHIJ
12150ABCCount Unique NumbersNumbersCount For #Value1Value2Value3Value4
22160kjl321502ABCmkn
32150mkn21602kjlHIK
42162GHI21621GHI
52160HIK
6
7
8
9
2007orEarlier
Excel 2010
Cell Formulas
RangeFormula
D2=SUMPRODUCT(--(FREQUENCY(A1:A5,A1:A5)>0))
F2=IF(E2="","",COUNTIF(\$A\$1:\$A\$5,E2))
F3=IF(E3="","",COUNTIF(\$A\$1:\$A\$5,E3))
F4=IF(E4="","",COUNTIF(\$A\$1:\$A\$5,E4))
F5=IF(E5="","",COUNTIF(\$A\$1:\$A\$5,E5))
F6=IF(E6="","",COUNTIF(\$A\$1:\$A\$5,E6))
F7=IF(E7="","",COUNTIF(\$A\$1:\$A\$5,E7))
F8=IF(E8="","",COUNTIF(\$A\$1:\$A\$5,E8))
F9=IF(E9="","",COUNTIF(\$A\$1:\$A\$5,E9))
#VALUE!
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.

That is:

In cell D2 enter this formula:

=SUMPRODUCT(--(FREQUENCY(A1:A5,A1:A5)>0))

In cell E2 enter this formula with the keystrokes Ctrl + Shift + Enter (not just Enter):

=IF(ROWS(E\$2:E2)>\$D\$2,"",INDEX(\$A\$1:\$A\$5,SMALL(IF(FREQUENCY(\$A\$1:\$A\$5,\$A\$1:\$A\$5),ROW(\$A\$1:\$A\$5)-ROW(\$A\$1)+1),ROWS(E\$2:E2))))

Then copy the formula down through E2:E9 (or however far you need it).

In cell F2 enter this formula:

=IF(E2="","",COUNTIF(\$A\$1:\$A\$5,E2))

Then copy the formula down through F2:F9 (or however far you need it).

In cell G2 enter this formula with the keystrokes Ctrl + Shift + Enter (not just Enter):

=IF(OR(COLUMNS(\$G2:G2)>\$F2,F2=""),"",INDEX(\$B\$1:\$B\$5,SMALL(IF(\$E2=\$A\$1:\$A\$5,ROW(\$A\$1:\$A\$5)-ROW(\$A\$1)+1),COLUMNS(\$G2:G2))))

Then copy the formula down through G2:J9 (or however far you need it).

#### mgirvin

##### Well-known Member
If you have Excel 2010, try this:

In cell D2 enter this formula:

=SUMPRODUCT(--(FREQUENCY(A1:A5,A1:A5)>0))

In cell E2 enter this non-array formula:

=IF(ROWS(E\$2:E2)>\$D\$2,"",INDEX(\$A\$1:\$A\$5,AGGREGATE(15,6,(ROW(\$A\$1:\$A\$5)-ROW(\$A\$1)+1)/(FREQUENCY(\$A\$1:\$A\$5,\$A\$1:\$A\$5)>0),ROWS(E\$2:E2))))

Then copy the formula down through E2:E9 (or however far you need it).

In cell F2 enter this formula:

=IF(E2="","",COUNTIF(\$A\$1:\$A\$5,E2))

Then copy the formula down through F2:F9 (or however far you need it).

In cell G2 enter this non-array formula:

=IF(OR(COLUMNS(\$G2:G2)>\$F2,F2=""),"",INDEX(\$B\$1:\$B\$5,AGGREGATE(15,6,(ROW(\$A\$1:\$A\$5)-ROW(\$A\$1)+1)/(\$E2=\$A\$1:\$A\$5),COLUMNS(\$G2:G2))))

Then copy the formula down through G2:J9 (or however far you need it).

Here's a picture:
Excel Workbook
ABCDEFGHIJ
12150ABCCount Unique NumbersNumbersCount For #Value1Value2Value3Value4
22160kjl321502ABCmkn
32150mkn21602kjlHIK
42162GHI21621GHI
52160HIK
6
7
8
9
2010
Excel 2010
Cell Formulas
RangeFormula
D2=SUMPRODUCT(--(FREQUENCY(A1:A5,A1:A5)>0))
E2=IF(ROWS(E\$2:E2)>\$D\$2,"",INDEX(\$A\$1:\$A\$5,AGGREGATE(15,6,(ROW(\$A\$1:\$A\$5)-ROW(\$A\$1)+1)/(FREQUENCY(\$A\$1:\$A\$5,\$A\$1:\$A\$5)>0),ROWS(E\$2:E2))))
E3=IF(ROWS(E\$2:E3)>\$D\$2,"",INDEX(\$A\$1:\$A\$5,AGGREGATE(15,6,(ROW(\$A\$1:\$A\$5)-ROW(\$A\$1)+1)/(FREQUENCY(\$A\$1:\$A\$5,\$A\$1:\$A\$5)>0),ROWS(E\$2:E3))))
E4=IF(ROWS(E\$2:E4)>\$D\$2,"",INDEX(\$A\$1:\$A\$5,AGGREGATE(15,6,(ROW(\$A\$1:\$A\$5)-ROW(\$A\$1)+1)/(FREQUENCY(\$A\$1:\$A\$5,\$A\$1:\$A\$5)>0),ROWS(E\$2:E4))))
E5=IF(ROWS(E\$2:E5)>\$D\$2,"",INDEX(\$A\$1:\$A\$5,AGGREGATE(15,6,(ROW(\$A\$1:\$A\$5)-ROW(\$A\$1)+1)/(FREQUENCY(\$A\$1:\$A\$5,\$A\$1:\$A\$5)>0),ROWS(E\$2:E5))))
E6=IF(ROWS(E\$2:E6)>\$D\$2,"",INDEX(\$A\$1:\$A\$5,AGGREGATE(15,6,(ROW(\$A\$1:\$A\$5)-ROW(\$A\$1)+1)/(FREQUENCY(\$A\$1:\$A\$5,\$A\$1:\$A\$5)>0),ROWS(E\$2:E6))))
E7=IF(ROWS(E\$2:E7)>\$D\$2,"",INDEX(\$A\$1:\$A\$5,AGGREGATE(15,6,(ROW(\$A\$1:\$A\$5)-ROW(\$A\$1)+1)/(FREQUENCY(\$A\$1:\$A\$5,\$A\$1:\$A\$5)>0),ROWS(E\$2:E7))))
E8=IF(ROWS(E\$2:E8)>\$D\$2,"",INDEX(\$A\$1:\$A\$5,AGGREGATE(15,6,(ROW(\$A\$1:\$A\$5)-ROW(\$A\$1)+1)/(FREQUENCY(\$A\$1:\$A\$5,\$A\$1:\$A\$5)>0),ROWS(E\$2:E8))))
E9=IF(ROWS(E\$2:E9)>\$D\$2,"",INDEX(\$A\$1:\$A\$5,AGGREGATE(15,6,(ROW(\$A\$1:\$A\$5)-ROW(\$A\$1)+1)/(FREQUENCY(\$A\$1:\$A\$5,\$A\$1:\$A\$5)>0),ROWS(E\$2:E9))))
F2=IF(E2="","",COUNTIF(\$A\$1:\$A\$5,E2))
F3=IF(E3="","",COUNTIF(\$A\$1:\$A\$5,E3))
F4=IF(E4="","",COUNTIF(\$A\$1:\$A\$5,E4))
F5=IF(E5="","",COUNTIF(\$A\$1:\$A\$5,E5))
F6=IF(E6="","",COUNTIF(\$A\$1:\$A\$5,E6))
F7=IF(E7="","",COUNTIF(\$A\$1:\$A\$5,E7))
F8=IF(E8="","",COUNTIF(\$A\$1:\$A\$5,E8))
F9=IF(E9="","",COUNTIF(\$A\$1:\$A\$5,E9))
G2=IF(OR(COLUMNS(\$G2:G2)>\$F2,F2=""),"",INDEX(\$B\$1:\$B\$5,AGGREGATE(15,6,(ROW(\$A\$1:\$A\$5)-ROW(\$A\$1)+1)/(\$E2=\$A\$1:\$A\$5),COLUMNS(\$G2:G2))))
G3=IF(OR(COLUMNS(\$G3:G3)>\$F3,F3=""),"",INDEX(\$B\$1:\$B\$5,AGGREGATE(15,6,(ROW(\$A\$1:\$A\$5)-ROW(\$A\$1)+1)/(\$E3=\$A\$1:\$A\$5),COLUMNS(\$G3:G3))))
G4=IF(OR(COLUMNS(\$G4:G4)>\$F4,F4=""),"",INDEX(\$B\$1:\$B\$5,AGGREGATE(15,6,(ROW(\$A\$1:\$A\$5)-ROW(\$A\$1)+1)/(\$E4=\$A\$1:\$A\$5),COLUMNS(\$G4:G4))))
G5=IF(OR(COLUMNS(\$G5:G5)>\$F5,F5=""),"",INDEX(\$B\$1:\$B\$5,AGGREGATE(15,6,(ROW(\$A\$1:\$A\$5)-ROW(\$A\$1)+1)/(\$E5=\$A\$1:\$A\$5),COLUMNS(\$G5:G5))))
G6=IF(OR(COLUMNS(\$G6:G6)>\$F6,F6=""),"",INDEX(\$B\$1:\$B\$5,AGGREGATE(15,6,(ROW(\$A\$1:\$A\$5)-ROW(\$A\$1)+1)/(\$E6=\$A\$1:\$A\$5),COLUMNS(\$G6:G6))))
G7=IF(OR(COLUMNS(\$G7:G7)>\$F7,F7=""),"",INDEX(\$B\$1:\$B\$5,AGGREGATE(15,6,(ROW(\$A\$1:\$A\$5)-ROW(\$A\$1)+1)/(\$E7=\$A\$1:\$A\$5),COLUMNS(\$G7:G7))))
G8=IF(OR(COLUMNS(\$G8:G8)>\$F8,F8=""),"",INDEX(\$B\$1:\$B\$5,AGGREGATE(15,6,(ROW(\$A\$1:\$A\$5)-ROW(\$A\$1)+1)/(\$E8=\$A\$1:\$A\$5),COLUMNS(\$G8:G8))))
G9=IF(OR(COLUMNS(\$G9:G9)>\$F9,F9=""),"",INDEX(\$B\$1:\$B\$5,AGGREGATE(15,6,(ROW(\$A\$1:\$A\$5)-ROW(\$A\$1)+1)/(\$E9=\$A\$1:\$A\$5),COLUMNS(\$G9:G9))))
H2=IF(OR(COLUMNS(\$G2:H2)>\$F2,G2=""),"",INDEX(\$B\$1:\$B\$5,AGGREGATE(15,6,(ROW(\$A\$1:\$A\$5)-ROW(\$A\$1)+1)/(\$E2=\$A\$1:\$A\$5),COLUMNS(\$G2:H2))))
H3=IF(OR(COLUMNS(\$G3:H3)>\$F3,G3=""),"",INDEX(\$B\$1:\$B\$5,AGGREGATE(15,6,(ROW(\$A\$1:\$A\$5)-ROW(\$A\$1)+1)/(\$E3=\$A\$1:\$A\$5),COLUMNS(\$G3:H3))))
H4=IF(OR(COLUMNS(\$G4:H4)>\$F4,G4=""),"",INDEX(\$B\$1:\$B\$5,AGGREGATE(15,6,(ROW(\$A\$1:\$A\$5)-ROW(\$A\$1)+1)/(\$E4=\$A\$1:\$A\$5),COLUMNS(\$G4:H4))))
H5=IF(OR(COLUMNS(\$G5:H5)>\$F5,G5=""),"",INDEX(\$B\$1:\$B\$5,AGGREGATE(15,6,(ROW(\$A\$1:\$A\$5)-ROW(\$A\$1)+1)/(\$E5=\$A\$1:\$A\$5),COLUMNS(\$G5:H5))))
H6=IF(OR(COLUMNS(\$G6:H6)>\$F6,G6=""),"",INDEX(\$B\$1:\$B\$5,AGGREGATE(15,6,(ROW(\$A\$1:\$A\$5)-ROW(\$A\$1)+1)/(\$E6=\$A\$1:\$A\$5),COLUMNS(\$G6:H6))))
H7=IF(OR(COLUMNS(\$G7:H7)>\$F7,G7=""),"",INDEX(\$B\$1:\$B\$5,AGGREGATE(15,6,(ROW(\$A\$1:\$A\$5)-ROW(\$A\$1)+1)/(\$E7=\$A\$1:\$A\$5),COLUMNS(\$G7:H7))))
H8=IF(OR(COLUMNS(\$G8:H8)>\$F8,G8=""),"",INDEX(\$B\$1:\$B\$5,AGGREGATE(15,6,(ROW(\$A\$1:\$A\$5)-ROW(\$A\$1)+1)/(\$E8=\$A\$1:\$A\$5),COLUMNS(\$G8:H8))))
H9=IF(OR(COLUMNS(\$G9:H9)>\$F9,G9=""),"",INDEX(\$B\$1:\$B\$5,AGGREGATE(15,6,(ROW(\$A\$1:\$A\$5)-ROW(\$A\$1)+1)/(\$E9=\$A\$1:\$A\$5),COLUMNS(\$G9:H9))))
I2=IF(OR(COLUMNS(\$G2:I2)>\$F2,H2=""),"",INDEX(\$B\$1:\$B\$5,AGGREGATE(15,6,(ROW(\$A\$1:\$A\$5)-ROW(\$A\$1)+1)/(\$E2=\$A\$1:\$A\$5),COLUMNS(\$G2:I2))))
I3=IF(OR(COLUMNS(\$G3:I3)>\$F3,H3=""),"",INDEX(\$B\$1:\$B\$5,AGGREGATE(15,6,(ROW(\$A\$1:\$A\$5)-ROW(\$A\$1)+1)/(\$E3=\$A\$1:\$A\$5),COLUMNS(\$G3:I3))))
I4=IF(OR(COLUMNS(\$G4:I4)>\$F4,H4=""),"",INDEX(\$B\$1:\$B\$5,AGGREGATE(15,6,(ROW(\$A\$1:\$A\$5)-ROW(\$A\$1)+1)/(\$E4=\$A\$1:\$A\$5),COLUMNS(\$G4:I4))))
I5=IF(OR(COLUMNS(\$G5:I5)>\$F5,H5=""),"",INDEX(\$B\$1:\$B\$5,AGGREGATE(15,6,(ROW(\$A\$1:\$A\$5)-ROW(\$A\$1)+1)/(\$E5=\$A\$1:\$A\$5),COLUMNS(\$G5:I5))))
I6=IF(OR(COLUMNS(\$G6:I6)>\$F6,H6=""),"",INDEX(\$B\$1:\$B\$5,AGGREGATE(15,6,(ROW(\$A\$1:\$A\$5)-ROW(\$A\$1)+1)/(\$E6=\$A\$1:\$A\$5),COLUMNS(\$G6:I6))))
I7=IF(OR(COLUMNS(\$G7:I7)>\$F7,H7=""),"",INDEX(\$B\$1:\$B\$5,AGGREGATE(15,6,(ROW(\$A\$1:\$A\$5)-ROW(\$A\$1)+1)/(\$E7=\$A\$1:\$A\$5),COLUMNS(\$G7:I7))))
I8=IF(OR(COLUMNS(\$G8:I8)>\$F8,H8=""),"",INDEX(\$B\$1:\$B\$5,AGGREGATE(15,6,(ROW(\$A\$1:\$A\$5)-ROW(\$A\$1)+1)/(\$E8=\$A\$1:\$A\$5),COLUMNS(\$G8:I8))))
I9=IF(OR(COLUMNS(\$G9:I9)>\$F9,H9=""),"",INDEX(\$B\$1:\$B\$5,AGGREGATE(15,6,(ROW(\$A\$1:\$A\$5)-ROW(\$A\$1)+1)/(\$E9=\$A\$1:\$A\$5),COLUMNS(\$G9:I9))))
J2=IF(OR(COLUMNS(\$G2:J2)>\$F2,I2=""),"",INDEX(\$B\$1:\$B\$5,AGGREGATE(15,6,(ROW(\$A\$1:\$A\$5)-ROW(\$A\$1)+1)/(\$E2=\$A\$1:\$A\$5),COLUMNS(\$G2:J2))))
J3=IF(OR(COLUMNS(\$G3:J3)>\$F3,I3=""),"",INDEX(\$B\$1:\$B\$5,AGGREGATE(15,6,(ROW(\$A\$1:\$A\$5)-ROW(\$A\$1)+1)/(\$E3=\$A\$1:\$A\$5),COLUMNS(\$G3:J3))))
J4=IF(OR(COLUMNS(\$G4:J4)>\$F4,I4=""),"",INDEX(\$B\$1:\$B\$5,AGGREGATE(15,6,(ROW(\$A\$1:\$A\$5)-ROW(\$A\$1)+1)/(\$E4=\$A\$1:\$A\$5),COLUMNS(\$G4:J4))))
J5=IF(OR(COLUMNS(\$G5:J5)>\$F5,I5=""),"",INDEX(\$B\$1:\$B\$5,AGGREGATE(15,6,(ROW(\$A\$1:\$A\$5)-ROW(\$A\$1)+1)/(\$E5=\$A\$1:\$A\$5),COLUMNS(\$G5:J5))))
J6=IF(OR(COLUMNS(\$G6:J6)>\$F6,I6=""),"",INDEX(\$B\$1:\$B\$5,AGGREGATE(15,6,(ROW(\$A\$1:\$A\$5)-ROW(\$A\$1)+1)/(\$E6=\$A\$1:\$A\$5),COLUMNS(\$G6:J6))))
J7=IF(OR(COLUMNS(\$G7:J7)>\$F7,I7=""),"",INDEX(\$B\$1:\$B\$5,AGGREGATE(15,6,(ROW(\$A\$1:\$A\$5)-ROW(\$A\$1)+1)/(\$E7=\$A\$1:\$A\$5),COLUMNS(\$G7:J7))))
J8=IF(OR(COLUMNS(\$G8:J8)>\$F8,I8=""),"",INDEX(\$B\$1:\$B\$5,AGGREGATE(15,6,(ROW(\$A\$1:\$A\$5)-ROW(\$A\$1)+1)/(\$E8=\$A\$1:\$A\$5),COLUMNS(\$G8:J8))))
J9=IF(OR(COLUMNS(\$G9:J9)>\$F9,I9=""),"",INDEX(\$B\$1:\$B\$5,AGGREGATE(15,6,(ROW(\$A\$1:\$A\$5)-ROW(\$A\$1)+1)/(\$E9=\$A\$1:\$A\$5),COLUMNS(\$G9:J9))))

#### harpreet123

##### Board Regular

Not sure why it didn't worked on xl2003. Also, I have more than 10 records for every post codes. It will be complicated to write formula for every column??

Harpreet

#### Smitty

##### Legend

Holy Crap Mike,

That had to keep you busy for a bit. #### hiker95

##### Well-known Member
harpreet123,

Sample data with your numbers beginning in A1, before the macro:

Excel Workbook
ABCDEF
12150ABC
22160kjl
32150mkn
42162ABC
52160HIK
6
Sheet1

After the macro:

Excel Workbook
ABCDEF
1NumbersValuesNumbersValue1Value2
22150ABC2150ABCmkn
32160kjl2160kjlHIK
42150mkn2162ABC
52162ABC
62160HIK
7
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below macro, by highlighting the macro code and pressing the keys CTRL + C
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Code:
``````Option Explicit
Sub ReorgData()
Dim LR As Long, LR2 As Long, a As Long, NC As Long, LC As Long
Dim c As Range, firstaddress As String
Application.ScreenUpdating = False
Range("A1").EntireRow.Insert
Range("A1").Resize(, 2).Value = [{"Numbers","Values"}]
LR = Cells(Rows.Count, "A").End(xlUp).Row
Range("A1:A" & LR).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("D1"), Unique:=True
LR2 = Cells(Rows.Count, "D").End(xlUp).Row
For a = 2 To LR2 Step 1
With Columns(1)
Set c = .Find(Cells(a, 4), LookIn:=xlValues, LookAt:=xlWhole)
If Not c Is Nothing Then
Do
NC = Cells(a, Columns.Count).End(xlToLeft).Column + 1
If NC > LC Then LC = NC
Cells(a, NC).Value = c.Offset(, 1).Value
Set c = .FindNext(c)
End If
End With
Next a
For a = 5 To LC Step 1
Cells(1, a) = "Value" & a - 4
Next a
Range(Cells(1, 4), Cells(LR, LC)).Columns.AutoFit
Application.ScreenUpdating = True
End Sub``````

Then run the "ReorgData" macro.

#### harpreet123

##### Board Regular

Now Thats called magic ...

Cheers it woked.
H

#### harpreet123

##### Board Regular
Just one more request ,.... if existing code can be changed little to identify the following :

I have list ... I am just using example below :

2160 g
2135 k
2365 9
3526 g
9585 u
2160 L
2365 o

Then I have completly different set of numbers which not all but some matches with above : for example only two
2160
2365

Can it return

2160 g L
2365 9 o

Many Thanks
H

#### hiker95

##### Well-known Member

Replies
3
Views
114
Replies
3
Views
160
Replies
3
Views
221
Replies
2
Views
199
Replies
5
Views
6K Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

### Forum statistics

1,163,550
Messages
5,832,418
Members
430,132
Latest member
Bdesai ### 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.

### Which adblocker are you using?    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

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