List records

[alliswell]

Hi everybody !
I am using excel 2007.
In column A i have client names and in column B are dates and in column M are totals in sheet2.

Now in sheet 1, in column A1 When i input name "John Parker" in A1 then i must get all records of John Parker with dates and totals in sheet1 A3,B3,C3

 

[kweaver]

Is that what you mean?

Book1
	A	B	C
1	John
2
3	John	6/29/2023	100
Sheet1

Cell Formulas
Range	Formula
A3	A3	=A1
B3	B3	=VLOOKUP($A3,Sheet2!$A$2:$M$5,2,0)
C3	C3	=VLOOKUP($A3,Sheet2!$A$2:$M$5,13,0)

Name

Date

Totals

John

6/29/2023​

100​

Mary

7/1/2023​

200​

Sue

7/12/2023​

250​

Bill

8/10/2023​

50​

 

[alliswell]

Is that what you mean?

Book1
	A	B	C
1	John
2
3	John	6/29/2023	100
Sheet1

Cell Formulas
Range	Formula
A3	A3	=A1
B3	B3	=VLOOKUP($A3,Sheet2!$A$2:$M$5,2,0)
C3	C3	=VLOOKUP($A3,Sheet2!$A$2:$M$5,13,0)

Name

Date

Totals

John

6/29/2023​

100​

Mary

7/1/2023​

200​

Sue

7/12/2023​

250​

Bill

8/10/2023​

5​

Sheet2
A B M
Names Dates Total
John Parker 13/01/1978 55
Alisha Bush 21/09/2022 190
John Parker 17/03/2023 117
michael Lee 11/06/2023 553
John Parker 23/06/2023 700

Sheet1
In A1 when i type John Parker in sheet1,
I must get all records of john parker only in sheet1 from A3 to how many john records are

John Parker 13/01/1978 55
John Parker 17/03/2023 117
John Parker 23/06/2024 700

Now there were 3 records of john so it must show like above

 

[kweaver]

That's considerably different from what you initially posted.

 

[kweaver]

How about this?

Sub multilookup()
Dim lr, i, j As Long
Sheets("Sheet1").Rows("4:100").EntireRow.Delete
lr = Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Row
j = 4
For i = 2 To lr
 If Sheets("Sheet2").Cells(i, 1) = Sheets("Sheet1").Cells(1, 1) Then
  Sheets("Sheet1").Cells(j, 1) = Sheets("Sheet1").Cells(1, 1)
  Sheets("Sheet1").Cells(j, 2) = Sheets("Sheet2").Cells(i, 2)
  Sheets("Sheet1").Cells(j, 3) = Sheets("Sheet2").Cells(i, 13)
  j = j + 1
 End If
Next i
Sheets("Sheet1").Range("B4:B100").NumberFormat = "dd/mm/yyyy"
End Sub

 

[alliswell]

How about this?

Sub multilookup()
Dim lr, i, j As Long
Sheets("Sheet1").Rows("4:100").EntireRow.Delete
lr = Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Row
j = 4
For i = 2 To lr
 If Sheets("Sheet2").Cells(i, 1) = Sheets("Sheet1").Cells(1, 1) Then
  Sheets("Sheet1").Cells(j, 1) = Sheets("Sheet1").Cells(1, 1)
  Sheets("Sheet1").Cells(j, 2) = Sheets("Sheet2").Cells(i, 2)
  Sheets("Sheet1").Cells(j, 3) = Sheets("Sheet2").Cells(i, 13)
  j = j + 1
 End If
Next i
Sheets("Sheet1").Range("B4:B100").NumberFormat = "dd/mm/yyyy"
End Sub

No i dont want vba
Is it possible through formula or function ?

 

[kweaver]

I don't believe so with 2007.

 

[alliswell]

I don't believe so with 2007.

Ok. Thanks kweaver

 

[awoohaw]

This should work in 2007.
It will sort the records by ascending date.
And if you move the first row of calculations to another row you must update the Row()-9, and Row()-10 integers in all of the formlas to the correct offset number value. And you must copy the formula down as many rows as you think you have number of records for one name, at least. I would not COPY it all the way down as it may slow your system up as these are fairly complex formulas.

Best wishes.

Book1
	A	B	C	D
1
2	Names	Dates	Total
3	John Parker	1978-01-13	55
4	Alisha Bush	2022-09-21	190
5	John Parker	2023-03-17	117
6	michael Lee	2023-06-11	553
7	John Parker	2023-06-23	700
8
9	John Parker
10	John Parker	1978-01-13	55
11	John Parker	2023-03-17	117
12	John Parker	2023-06-23	700
13
Sheet2

Cell Formulas
Range	Formula
A10:A13	A10	=IF(COUNTIF($A$3:$A$7,A9)<(ROW()-9),"",A9)
B10:B13	B10	=IF(COUNTIF($A$3:$A$7,A9)<(ROW()-9),"", LARGE(($A10=$A$3:$A$7)*($B$3:$B$7), COUNTIF($A$3:$A$7,$A9)-(ROW()-10)))
C10:C13	C10	=IF(COUNTIF($A$3:$A$7,A9)<(ROW()-9),"",INDEX($C$3:$C$7,MATCH($A10&$B10,$A$3:$A$7&$B$3:$B$7,0)))

 

[AhoyNC]

Try the formula below:
Also, take a look at ADAVANCED FILTER in the menu which may be the better way to go.

The formula in A4 is an array formula and must be entered with CTRL-SHIFT-ENTER, then drag down the rows as needed.
Formulas in B4 to D4 can be enter with just ENTER, then drag down as needed.

Sheet1

Book3
	A	B	C	D
1	Name:	John Parker
2	# of Times	3
3	Ref#	Name	Date	Totals
4	1	John Parker	1/13/78	55
5	3	John Parker	3/17/23	117
6	5	John Parker	6/23/23	700
Sheet1

Cell Formulas
Range	Formula
B2	B2	=COUNTIF(Sheet2!$A$2:$A$6,$B$1)
A4:A6	A4	=IF(ROWS($A$4:A4)>$B$2,"",SMALL(IF($B$1=Sheet2!$A$2:$A$6,ROW(Sheet2!$A$2:$A$6)-ROW(Sheet2!$A$2)+1),ROWS($A$4:A4)))
B4:B6	B4	=IF($A4="","",INDEX(Sheet2!$A$2:$A$6,$A4,1))
C4:C6	C4	=IF($A4="","",INDEX(Sheet2!$B$2:$B$6,$A4,1))
D4:D6	D4	=IF($A4="","",INDEX(Sheet2!$M$2:$M$6,$A4,1))
Press CTRL+SHIFT+ENTER to enter array formulas.

Sheet2

Book3
	A	B	C										M
1	Name	Date											Total
2	John Parker	1/13/1978											55
3	Alisha Bush	9/21/2022											190
4	John Parker	3/17/2023											117
5	michael Lee	6/11/2023											553
6	John Parker	6/23/2023											700
Sheet2