Lookup number from Table with multiple entries

[kachaloo]

Hello
I am struggling to find a solution and I really need please.

I have sheet as below
-----A-----B------C---------D-----------
1---NO---Firm----Name----Address-----
2---A1---Tesco--John----
3---A2---Asda---Smith---
4---A3---Tesco--Nick----
5---A4---Aldi----Mary---
6---A5---Asda---Jean---
7---A6---Asda---Mark

I want to return the NO (A2,A5,A6) for Asda to another sheet (Asda Wages) where I have 10 Cells like A1,A2,,,A9,A10
In Cell A1 I want A2
Cell A2 I want A5
Cell A3 I want A6
Ceall A4,,A10 should return 0.

Any help is appreciated.

Regards

 

[taurean]

I do not know if this can be achieved by formula but PivotTable can give you this and VBA can also generate this:

Public Sub ListSpecifics()
Dim lLastRow As Long
Dim sSearchString As String
sSearchString = Application.InputBox(Prompt:="Please Select Range", Type:=8).Value2
lLastRow = Sheet1.Range("B" & Rows.Count).End(xlUp).Row
For i = 2 To lLastRow Step 1
    With Sheet1
        If .Range("B" & i).Value2 = sSearchString Then
        Sheet2.Range("A" & Rows.Count).End(xlUp)(2).Value2 = .Range("A" & i).Value2
        End If
    End With
Next i
End Sub

 

[T. Valko]

Hello
I am struggling to find a solution and I really need please.

I have sheet as below
-----A-----B------C---------D-----------
1---NO---Firm----Name----Address-----
2---A1---Tesco--John----
3---A2---Asda---Smith---
4---A3---Tesco--Nick----
5---A4---Aldi----Mary---
6---A5---Asda---Jean---
7---A6---Asda---Mark

I want to return the NO (A2,A5,A6) for Asda to another sheet (Asda Wages) where I have 10 Cells like A1,A2,,,A9,A10
In Cell A1 I want A2
Cell A2 I want A5
Cell A3 I want A6
Ceall A4,,A10 should return 0.

Any help is appreciated.

Regards

Try this...

Book1
	A	B
1	NO	Firm
2	A1	Tesco
3	A2	Asda
4	A3	Tesco
5	A4	Aldi
6	A5	Asda
7	A6	Asda
Sheet1

Book1
	A	B	C
1	Firm	Count	NO
2	Asda	3	A2
3	_	_	A5
4	_	_	A6
5	_	_	0
6	_	_	0
7	_	_	0
8	_	_	0
9	_	_	0
10	_	_	0
11	_	_	0
Sheet2

In the formulas I use the following named ranges:

• Name: NO
• Refers to: =Sheet1!$A:$A
• Name: Firm
• Refers to: =Sheet1!$B$2:$B$7

This formula entered in B2. This will return the count of records for the firm of interest.

=COUNTIF(Firm,A2)

Enter this array formula** in C2. This will extract the NO for the firm of interest.

=IF(ROWS(C$2:C2)>B$2,0,INDEX(NO,SMALL(IF(Firm=A$2,ROW(Firm)),ROWS(C$2:C2))))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Copy down a total of 10 cells to C11.

 

[kachaloo]

This formula entered in B2. This will return the count of records for the firm of interest.

=COUNTIF(Firm,A2)

Enter this array formula** in C2. This will extract the NO for the firm of interest.

=IF(ROWS(C$2:C2)>B$2,0,INDEX(NO,SMALL(IF(Firm=A$2,ROW(Firm)),ROWS(C$2:C2))))

Thanks guys really appreciated.
I have tried the Pivot Table option before but I could not manage to get the output in order to another sheet.

However I have found the later suggestion by Valko exactly what I was looking for.

I like formula like these that I can see
I have to transposed the table in sheet 2 to get the No in order and used HLOOKUP to get the No in another sheet where the wages are calculated and wages slips are generated.

thanks Valko and Taurean

 

[T. Valko]

Thanks guys really appreciated.
I have tried the Pivot Table option before but I could not manage to get the output in order to another sheet.

However I have found the later suggestion by Valko exactly what I was looking for.

I like formula like these that I can see
I have to transposed the table in sheet 2 to get the No in order and used HLOOKUP to get the No in another sheet where the wages are calculated and wages slips are generated.

thanks Valko and Taurean

You're welcome. Thanks for the feedback!