# 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

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
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).

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))))

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

Holy Crap Mike,

That had to keep you busy for a bit.

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.

Now Thats called magic ...

Cheers it woked.
H

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

Replies
1
Views
346
Replies
13
Views
229
Replies
2
Views
742
Replies
3
Views
293
Replies
1
Views
206

1,217,382
Messages
6,136,242
Members
450,000
Latest member
jgp19

### 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