Lookup formula to return multiple values with same identifier

I

irfananeeza

Active Member
Joined
Feb 15, 2008
Messages
334
Office Version
  1. 2010
Hi folks,
Can someone help me to resolve the following problem. I need some sort of lookup formula that can accomplish the following. As you can see the vlookup formula does not work here. Also, this is just a sample data. My data is almost 20000 rows and and have columns A - H. In other words, its huge so any formula that can do the job efficiently would be greatly appreciated.
Sheet1
Proj# Customer Amount
356699 BearingPoint 10.00
356699 Bearingpoint 20.00
356699 Bearingpoint 22.00
357777 TeckInc 33.00
357777 TeckInc 34.50
425555 NexusLtd 35.50
425555 NexusLtd 42.75
425555 NexusLtd 33.75
425555 NexusLtd 63.00
425555 NexusLtd 54.00
425555 NexusLtd 55.00
112233 GoldenEagle 23.00
112233 GoldenEagle 44.00
112233 GoldenEage 75.00

Result Expected:
Sheet2
Proj# Customer
425555 NexusLtd 35.50
425555 NexusLtd 42.75
425555 NexusLtd 33.75
425555 NexusLtd 63.00
425555 NexusLtd 54.00
425555 NexusLtd 55.00
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
Sort by date Sort by votes
irfananeeza,

You can use Data Filter, Advanced Filter, with Criter and Extract ranges on Sheet2:


Before the macro:


Excel Workbook
ABC
1Proj#CustomerAmount
2356699BearingPoint10.00
3356699Bearingpoint20.00
4356699Bearingpoint22.00
5357777TeckInc33.00
6357777TeckInc34.50
7425555NexusLtd35.50
8425555NexusLtd42.75
9425555NexusLtd33.75
10425555NexusLtd63.00
11425555NexusLtd54.00
12425555NexusLtd55.00
13112233GoldenEagle23.00
14112233GoldenEagle44.00
15112233GoldenEage75.00
Sheet1



Excel Workbook
AB
1Criteria*
2Proj#Amount
3**
4**
5**
6**
7**
8**
9Extract*
10Proj#Amount
11**
12**
13**
14**
15**
16**
17**
Sheet2



Enter in Sheet2, cell A3 425555A and then run the macro:


Excel Workbook
AB
1Criteria*
2Proj#Amount
3425555*
4**
5**
6**
7**
8**
9Extract*
10Proj#Amount
1142555535.50
1242555542.75
1342555533.75
1442555563.00
1542555554.00
1642555555.00
17**
Sheet2



Then change Sheet2 cell A3 to 356699, and run the macro again:


Excel Workbook
AB
1Criteria*
2Proj#Amount
3356699*
4**
5**
6**
7**
8**
9Extract*
10Proj#Amount
1135669910.00
1235669920.00
1335669922.00
14**
15**
16**
17**
Sheet2



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.


Code: 
Option Explicit
Sub Filter()
'
' Shortcut key:  CTRL + SHIFT + F
'
Dim DataTable As Range
Application.ScreenUpdating = False
Sheets("Sheet2").Range("A11:B1000").ClearContents
With Sheets("Sheet1")
  Set DataTable = .Range("A1").Resize(.UsedRange.Rows.Count, .UsedRange.Columns.Count)
End With
DataTable.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range("Criteria"), CopyToRange:=Range("Extract")
Sheets("Sheet2").Range("C3").Select
Application.ScreenUpdating = True
End Sub


Setup Sheet2 as shown.

Then highlite range A2:B3, and click in the box above the A column marker, and enter/type in Criteria


Then highlite range A10:B1000, and click in the box above the A column marker, and enter/type in Extract


Then click on Tools, Macro, Macros...
Click on the macro name Filter

and click on the Options...[/] button.

in the first small box below Shortcut key: type in F

then click on the OK button,

and then in the displayed Macro box, click on the upper right RED X.

To run the macro after you have entered a Proj# in cell A3:

Press and hold down the CTRL and the SHIFT keys, and press the F key.


Or, just run the "Filter" macro.
 
Upvote 0
Hi Folks,

Thanks for the help here. I analyzed Aladin formula using columns instead of rows and when applied it does not work. What am I doing wrong here.

=COUNTIF($A$1:$A$8,D1)

=IF(COLUMNS(F$1:F1)<=$E1,INDEX($B$1:$B$8,SMALL(IF($A$1:$A$8=$D1,COLUMN($B$1:$B$8)-COLUMN($B$1)+1),COLUMNS(F$1:F1))),"")

<TABLE style="WIDTH: 336pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=448 border=0 x:str><COLGROUP><COL style="WIDTH: 48pt" span=7 width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=64 height=17>A</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=64>B</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=64>C</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=64>D</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=64>E</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=64>F</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=64>G</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>a</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Irfan</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">a</TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" x:fmla="=COUNTIF($A$2:$A$9,D2)" x:num>2</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" x:fmla='=IF(COLUMNS(F$2:F2)<=$E2,INDEX($B$2:$B$9,SMALL(IF($A$2:$A$9=$D2,COLUMN($B$2:$B$9)-COLUMN($B$2)+1),COLUMNS(F$2:F2))),"")' x:arrayrange="F2">Irfan</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" x:fmla='=IF(COLUMNS(G$2:G2)<=$E2,INDEX($B$2:$B$9,SMALL(IF($A$2:$A$9=$D2,COLUMN($B$2:$B$9)-COLUMN($B$2)+1),COLUMNS(G$2:G2))),"")' x:arrayrange="G2">Irfan</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>b</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">kamra</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">b</TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" x:fmla="=COUNTIF($A$2:$A$9,D3)" x:num>2</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" x:fmla='=IF(COLUMNS(F$2:F3)<=$E3,INDEX($B$2:$B$9,SMALL(IF($A$2:$A$9=$D3,COLUMN($B$2:$B$9)-COLUMN($B$2)+1),COLUMNS(F$2:F3))),"")' x:arrayrange="F3">Irfan</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" x:fmla='=IF(COLUMNS(G$2:G3)<=$E3,INDEX($B$2:$B$9,SMALL(IF($A$2:$A$9=$D3,COLUMN($B$2:$B$9)-COLUMN($B$2)+1),COLUMNS(G$2:G3))),"")' x:arrayrange="G3">Irfan</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>c</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">ahmed</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">c</TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" x:fmla="=COUNTIF($A$2:$A$9,D4)" x:num>2</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" x:fmla='=IF(COLUMNS(F$2:F4)<=$E4,INDEX($B$2:$B$9,SMALL(IF($A$2:$A$9=$D4,COLUMN($B$2:$B$9)-COLUMN($B$2)+1),COLUMNS(F$2:F4))),"")' x:arrayrange="F4">Irfan</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" x:fmla='=IF(COLUMNS(G$2:G4)<=$E4,INDEX($B$2:$B$9,SMALL(IF($A$2:$A$9=$D4,COLUMN($B$2:$B$9)-COLUMN($B$2)+1),COLUMNS(G$2:G4))),"")' x:arrayrange="G4">Irfan</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>d</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">yoland</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">d</TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" x:fmla="=COUNTIF($A$2:$A$9,D5)" x:num>1</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" x:fmla='=IF(COLUMNS(F$2:F5)<=$E5,INDEX($B$2:$B$9,SMALL(IF($A$2:$A$9=$D5,COLUMN($B$2:$B$9)-COLUMN($B$2)+1),COLUMNS(F$2:F5))),"")' x:arrayrange="F5">Irfan</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" x:fmla='=IF(COLUMNS(G$2:G5)<=$E5,INDEX($B$2:$B$9,SMALL(IF($A$2:$A$9=$D5,COLUMN($B$2:$B$9)-COLUMN($B$2)+1),COLUMNS(G$2:G5))),"")' x:arrayrange="G5">Irfan</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>e</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">marg</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">e</TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" x:fmla="=COUNTIF($A$2:$A$9,D6)" x:num>1</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" x:fmla='=IF(COLUMNS(F$2:F6)<=$E6,INDEX($B$2:$B$9,SMALL(IF($A$2:$A$9=$D6,COLUMN($B$2:$B$9)-COLUMN($B$2)+1),COLUMNS(F$2:F6))),"")' x:arrayrange="F6">Irfan</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" x:fmla='=IF(COLUMNS(G$2:G6)<=$E6,INDEX($B$2:$B$9,SMALL(IF($A$2:$A$9=$D6,COLUMN($B$2:$B$9)-COLUMN($B$2)+1),COLUMNS(G$2:G6))),"")' x:arrayrange="G6">Irfan</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>a</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">ada</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">a</TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" x:fmla="=COUNTIF($A$2:$A$9,D7)" x:num>2</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" x:fmla='=IF(COLUMNS(F$2:F7)<=$E7,INDEX($B$2:$B$9,SMALL(IF($A$2:$A$9=$D7,COLUMN($B$2:$B$9)-COLUMN($B$2)+1),COLUMNS(F$2:F7))),"")' x:arrayrange="F7">Irfan</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" x:fmla='=IF(COLUMNS(G$2:G7)<=$E7,INDEX($B$2:$B$9,SMALL(IF($A$2:$A$9=$D7,COLUMN($B$2:$B$9)-COLUMN($B$2)+1),COLUMNS(G$2:G7))),"")' x:arrayrange="G7">Irfan</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>b</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">sandy</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">b</TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" x:fmla="=COUNTIF($A$2:$A$9,D8)" x:num>2</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" x:fmla='=IF(COLUMNS(F$2:F8)<=$E8,INDEX($B$2:$B$9,SMALL(IF($A$2:$A$9=$D8,COLUMN($B$2:$B$9)-COLUMN($B$2)+1),COLUMNS(F$2:F8))),"")' x:arrayrange="F8">Irfan</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" x:fmla='=IF(COLUMNS(G$2:G8)<=$E8,INDEX($B$2:$B$9,SMALL(IF($A$2:$A$9=$D8,COLUMN($B$2:$B$9)-COLUMN($B$2)+1),COLUMNS(G$2:G8))),"")' x:arrayrange="G8">Irfan</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>c</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">hoa</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">c</TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" x:fmla="=COUNTIF($A$2:$A$9,D9)" x:num>2</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" x:fmla='=IF(COLUMNS(F$2:F9)<=$E9,INDEX($B$2:$B$9,SMALL(IF($A$2:$A$9=$D9,COLUMN($B$2:$B$9)-COLUMN($B$2)+1),COLUMNS(F$2:F9))),"")' x:arrayrange="F9">Irfan</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" x:fmla='=IF(COLUMNS(G$2:G9)<=$E9,INDEX($B$2:$B$9,SMALL(IF($A$2:$A$9=$D9,COLUMN($B$2:$B$9)-COLUMN($B$2)+1),COLUMNS(G$2:G9))),"")' x:arrayrange="G9">Irfan</TD></TR></TBODY></TABLE>
 
Last edited:
Upvote 0
irfananeeza,

I missed something.

Before the macro:


Excel Workbook
ABC
1Proj#CustomerAmount
2356699BearingPoint10.00
3356699Bearingpoint20.00
4356699Bearingpoint22.00
5357777TeckInc33.00
6357777TeckInc34.50
7425555NexusLtd35.50
8425555NexusLtd42.75
9425555NexusLtd33.75
10425555NexusLtd63.00
11425555NexusLtd54.00
12425555NexusLtd55.00
13112233GoldenEagle23.00
14112233GoldenEagle44.00
15112233GoldenEage75.00
16
Sheet1



Excel Workbook
ABC
1Criteria
2Proj#CustomerAmount
3
4
5
6
7
8
9Extract
10Proj#CustomerAmount
11
12
13
14
15
16
17
Sheet2



After the macro:


Excel Workbook
ABC
1Criteria
2Proj#CustomerAmount
3425555
4
5
6
7
8
9Extract
10Proj#CustomerAmount
11425555NexusLtd35.50
12425555NexusLtd42.75
13425555NexusLtd33.75
14425555NexusLtd63.00
15425555NexusLtd54.00
16425555NexusLtd55.00
17
Sheet2



Then change Sheet2 cell A3 to 356699, and run the macro again:


Excel Workbook
ABC
1Criteria
2Proj#CustomerAmount
3356699
4
5
6
7
8
9Extract
10Proj#CustomerAmount
11356699BearingPoint10.00
12356699Bearingpoint20.00
13356699Bearingpoint22.00
14
15
16
17
Sheet2




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.

Code: 
Option Explicit
Sub Filter()
'
' Shortcut key:  CTRL + SHIFT + F
'
Dim DataTable As Range
Application.ScreenUpdating = False
Sheets("Sheet2").Range("A11:C1000").ClearContents
With Sheets("Sheet1")
  Set DataTable = .Range("A1").Resize(.UsedRange.Rows.Count, .UsedRange.Columns.Count)
End With
DataTable.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range("Criteria"), CopyToRange:=Range("Extract")
Sheets("Sheet2").Range("D3").Select
Application.ScreenUpdating = True
End Sub


Setup Sheet2 as shown.

Then highlite range A2:C3, and click in the box above the A column marker, and enter/type in Criteria


Then highlite range A10:C1000, and click in the box above the A column marker, and enter/type in Extract


Then click on Tools, Macro, Macros...
Click on the macro name Filter

and click on the Options... button.

in the first small box below Shortcut key: type in F

then click on the OK button,

and then in the displayed Macro box, click on the upper right RED X.

To run the macro after you have entered a Proj# in cell A3:

Press and hold down the CTRL and the SHIFT keys, and press the F key.


Or, just run the "Filter" macro.
 
Last edited:
Upvote 0
irfananeeza said:
Hi folks,
Can someone help me to resolve the following problem. I need some sort of lookup formula that can accomplish the following. As you can see the vlookup formula does not work here. Also, this is just a sample data. My data is almost 20000 rows and and have columns A - H. In other words, its huge so any formula that can do the job efficiently would be greatly appreciated.
Sheet1
Proj# Customer Amount
356699 BearingPoint 10.00
356699 Bearingpoint 20.00
356699 Bearingpoint 22.00
357777 TeckInc 33.00
357777 TeckInc 34.50
425555 NexusLtd 35.50
425555 NexusLtd 42.75
425555 NexusLtd 33.75
425555 NexusLtd 63.00
425555 NexusLtd 54.00
425555 NexusLtd 55.00
112233 GoldenEagle 23.00
112233 GoldenEagle 44.00
112233 GoldenEage 75.00

Result Expected:
Sheet2
Proj# Customer
425555 NexusLtd 35.50
425555 NexusLtd 42.75
425555 NexusLtd 33.75
425555 NexusLtd 63.00
425555 NexusLtd 54.00
425555 NexusLtd 55.00
Click to expand...

Let A1:C15 on Sheet1 house the sample you posted.

Sheet2

<TABLE style="WIDTH: 146pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=194 x:str><COLGROUP><COL style="WIDTH: 47pt; mso-width-source: userset; mso-width-alt: 2304" width=63><COL style="WIDTH: 53pt; mso-width-source: userset; mso-width-alt: 2560" width=70><COL style="WIDTH: 46pt; mso-width-source: userset; mso-width-alt: 2230" width=61><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 47pt; HEIGHT: 12.75pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=17 width=63>Proj#</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 53pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 width=70>Count</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 46pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 width=61> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=17 align=right x:num>425555</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 align=right x:num>6</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 height=17>Idx</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25>Customer</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25>Amount</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=17 align=right x:num>6</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24>NexusLtd</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 align=right x:num>35.5</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=17 align=right x:num>7</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24>NexusLtd</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 align=right x:num>42.75</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=17 align=right x:num>8</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24>NexusLtd</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 align=right x:num>33.75</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=17 align=right x:num>9</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24>NexusLtd</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 align=right x:num>63</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=17 align=right x:num>10</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24>NexusLtd</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 align=right x:num>54</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=17 align=right x:num>11</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24>NexusLtd</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 align=right x:num>55</TD></TR></TBODY></TABLE>

A2: 425555

B2:

=COUNTIF(Sheet1!$A$2:$A$15,A$2)

1) Activate Insert|Name|Define.

2) Enter Rvec in the Names in Workbook box.

3) Enter in the Refers to box:

=ROW(Sheet1!$A$2:$A$15)-ROW(Sheet1!$A$2)+1

4) Click OK.

A4:

Control+shift+enter, not just enter...

=IF(ROWS($A$4:A4)<=$B$2,SMALL(IF(Sheet1!$A$2:$A$15=$A$2,Rvec),ROWS($A$4:A4)),"")

and copy down.

B4, just enter, copy across, and down:

=IF(N($A4),INDEX(Sheet1!B$2:B$15,$A4),"")
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Solved
Lookup based on multiple criteria
2 3
Replies
22
Views
860
paulkc
P
F
  • Solved
Get previous 6 months bottom 25% monthly average values
Replies
7
Views
452
Fluff
Fluff
J
  • Question Question
Rolling Year to Date Figures
Replies
4
Views
1K
JamesCES
J
G
  • Question Question
Multiple lookup
Replies
6
Views
442
Aladin Akyurek
Aladin Akyurek
C
  • Question Question
Multiple selections in a cell to return different values
Replies
0
Views
351
CrustyR1
C

Forum statistics

Threads
1,223,247
Messages
6,171,007
Members
452,374
Latest member
keccles

Share this page

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