Tricky Situation

harpreet123

Board Regular
Joined
Nov 1, 2009
Messages
61
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
 

Some videos you may like

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

mgirvin

Well-known Member
Joined
Dec 15, 2005
Messages
1,213
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
Joined
Dec 15, 2005
Messages
1,213
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
Joined
Nov 1, 2009
Messages
61
Thanks maty.... appreciate your effort.

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
Joined
May 15, 2003
Messages
29,536

ADVERTISEMENT

Holy Crap Mike,

That had to keep you busy for a bit. :)
 

hiker95

Well-known Member
Joined
Apr 8, 2009
Messages
17,649
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).

Adding the Macro
1. Copy the below macro, by highlighting the macro code and pressing the keys CTRL + C
2. Open your workbook
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()
' hiker95, 7/20/2010, http://www.mrexcel.com/forum/showthread.php?t=482556
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
      firstaddress = c.Address
      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)
      Loop While Not c Is Nothing And c.Address <> firstaddress
    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
Joined
Nov 1, 2009
Messages
61

ADVERTISEMENT

Now Thats called magic :)...

Cheers it woked.
H
 

harpreet123

Board Regular
Joined
Nov 1, 2009
Messages
61
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,947
Messages
5,599,012
Members
414,273
Latest member
vikas007

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
Top