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
 

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
Joined
Dec 15, 2005
Messages
1,216
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,216
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
 
Master Excel Bundle

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

Threads
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.
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