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

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).
 
Upvote 0
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))))
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

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