Looking to pull data out of a report for multiple employees

[YoSquidly]

Thank you all in advance for reviewing and giving input on this.
I have attached a section of a report that is generated for payroll. I pulled it into excel and am trying to pull data off it to make payroll easier for me.
On the right side of the 'Grey Bar' is what I am trying to pull and on the left side is the report
My logic is:
Find employee ID listed in K3 in Col A (yellow highlight)
Jump over to Col B and find next instance of '*TOTAL*' (orange highlight)
Jump over to Col D in same row and retrieve data ('29.5' highlight red) and store in L3

I have tried combinations of CELL/INDEX/MATCH to achieve this, but coming up short; I don't think VLOOKUP can do this, unless I nest it somehow (I failed on that also)
INDEX/MATCH I think would work great if the employee data was listed in all cells in col A, but the report does not generate that format.

Ideas??

 

[J.Ty.]

Honestly, your spreadsheet has very unfortunate design. It violates the principle of First Normal Form in many ways. This principle says that there should be one piece of information per one cell.

• In column A you have some cells which contain multiple pieces of information: name and surname combined with a number (emplyee number?). Strictly speaking, you should also sperate name from surname, because reconstructing the form "name,surname" from "name surname" you want to use as a query is not an easy task. Think of people with double given names and double surnames: how do you distinguish those two cases?
• In column A you have also cells with omitted information which should be there: empty cells with the intended meaning "the same as before".

All those flaws make your spreadsheet very difficult to operate. You seem to know sufficiently much about formulas to be able to do all you need, were the spreadsheet free from the design flaws.

Therefore I strongly recommend that you redesign it.

J.Ty.

 

[Peter_SSs]

Therefore I strongly recommend that you redesign it.

I'm guessing that may not be easily (or at all) possible given that the data is coming from another system (that the OP may have no control over) ..

I pulled it into excel .....
I think would work great if the employee data was listed in all cells in col A, but the report does not generate that format.

@YoSquidly
Welcome to the MrExcel board!

See if this heads you in the right direction.

A_MrExcel.xlsm
	A	B	C	D	E						K	L
1
2
3											12345	29.5
4											98765	54.3
5
6
7
8
9
10
11
12
13
14		*TOTAL*		0.9
15
16
17	12345 JOHNSON,DON
18
19
20
21
22
23
24
25		*TOTAL*		29.5
26
27
28	98765 CRAIG,DAN
29
30
31
32
33
34
35
36
37
38
39
40		*TOTAL*		54.3
41
Lookup Totals

Cell Formulas
Range	Formula
L3:L4	L3	=INDEX(D$1:D$100,MATCH("*TOTAL*",INDEX(B:B,MATCH(K3&"*",A:A,0)):B$100,0)+MATCH(K3&"*",A:A,0)-1)

 

[YoSquidly]

I'm guessing that may not be easily (or at all) possible given that the data is coming from another system (that the OP may have no control over) ..


@YoSquidly
Welcome to the MrExcel board!

See if this heads you in the right direction.

Thank You very much.
It was a perfect direction.
I tried it this morning and it works great; found each employee and the data i was looking for.

I have used this community over the years to find answers. First time I'm submitting a question. It took me a few weeks to eat my pride, but I'm glad I did.

Thanks again

 

[Peter_SSs]

First time I'm submitting a question. It took me a few weeks to eat my pride, but I'm glad I did.

Most of us, including me, started that way but it is what the forum is for.

Thank You very much.
It was a perfect direction.
I tried it this morning and it works great; found each employee and the data i was looking for.

You're welcome. Glad it helped you get sorted.


BTW, for the future, better to provide your sample data in a form that helpers can copy/paste to test with so they don'y have to manually type from an image. My signature block below has help with that.

 

[Peter_SSs]

Actually, you mentioned possibly using VLOOKUP & perhaps I should have investigated that a bit earlier as that provides probably a simpler way.

A_MrExcel.xlsm
	A	B	C	D	E						K	L
3											12345	29.5
4											98765	54.3
5
6
7
8
9
10
11
12
13
14		*TOTAL*		0.9
15
16
17	12345 JOHNSON,DON
18
19
20
21
22
23
24
25		*TOTAL*		29.5
26
27
28	98765 CRAIG,DAN
29
30
31
32
33
34
35
36
37
38
39
40		*TOTAL*		54.3
41
Lookup Totals

Cell Formulas
Range	Formula
L3:L4	L3	=VLOOKUP("*TOTAL*",INDEX(B:B,MATCH(K3&"*",A:A,0)):D$100,3,0)

 

[YoSquidly]

Actually, you mentioned possibly using VLOOKUP & perhaps I should have investigated that a bit earlier as that provides probably a simpler way.

This worked nicely. I like this way because more. I just could not wrap my head around how to get the range to change based on location of ID/Name. I would have expected the INDEX statement to return the value of the cell (which it does if you separate it out), but it actually returns a location that VLOOKUP uses. I like it. At least that's my take on it.
Thank You Again

 

[Peter_SSs]

Cheers.