HOw to use, IF, AND, Lookup, match, maybe all or none of these!

[Robert1290]

Good evening/morning,

I could do with some help on the example workbook I have added. What I am trying to acheive is this:-

Include all of the relevant invoice numbers from sheet 2, onto sheet one if the employee number and period number matches on both worksheets. I want this to work and include all of the hours for the 2 tests on sheet 2, matching the total hours worked on sheet 1. If the total hours on sheet 1 does not match the breakdown on sheet 2 (providing the emp number and period number also match), then "investigate".

So, for example, employee 1001 on period 1 should have invoice numbers 2001 & 2007 returned.

My brain is too fried to work this out tonight.

Many thanks in advance

[#2]

@Robert1290 

=IF(SUMIFS(Sheet2!$C$2:$C$10,Sheet2!$A$2:$A$10,Sheet1!$A2,Sheet2!$B$2:$B$10,Sheet1!$B2)<>Sheet1!$C2,"Investigate",IFERROR(INDEX(Sheet2!$D$2:$D$10,SMALL(IF((Sheet2!$A$2:$A$10=Sheet1!$A2)*(Sheet2!$B$2:$B$10=Sheet1!$B2),ROW(Sheet2!$D$1:$D$9)),COLUMN(Sheet1!A1))),""))

You can try this formula. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel 2021. The formula is in cell D2 and filled across range D2:H5.

[#3]

@Robert1290 

With data model pivot and DAX measure.

[#4]

Thank you @OliverScheurich, this works on the sample workbook fine, but when I apply it to the real workbook it doesn't work. I think it may be that there is some data missing from some cells which breaks the formulas perhaps? I am going to see if the other answer given can create the pivot table and DAX, as that is alien to me. I will keep trying with your answer too, I think i have the formula all correct, so will keep plugging at it for now.

[#5]

Thanks Detlef Lewin, any chance of a video to show me how to set this up?

[#6]

@Robert1290 

There is this thing called web search.

https://www.google.com/search?q=add+data+model+pivot+table

https://www.google.com/search?q=add+text+to+values+area+of+a+pivot+table

[#7]

@Detlef Lewin
Many thanks for this. I did turn to google in the first ance before my previous reply, and have looked at the 2 pages you have sent. As I am a complete rookie on pivot tables, I have struggled puttting it together like you have. I am able to make a pivot table, but need help getting the data from sheet 2, and really do not understand DAX measure. Are you able to provide a step by step guide at all please?

I am getting the error 'a table can''t overlap another table'

[#8]

@Robert1290 

The web search gives you many step by step tutorials either as text or as video.

It's all there and I could not explain it better than those experts.

And regarding your error: Your pivot table needs more rows/columns and there are not enough blank rows/columns between your pivot tables.

Either add more blank rows/columns between your pivot tables or move one of the pivot tables onto another sheet.

https://www.google.com/search?q=a+table+can%27%27t+overlap+another+table

https://www.google.com/search?q=move+a+pivot+table+onto+another+sheet

[#9]

@OliverScheurich
This now works, thank you. There was problems in the real data, and also with formatting.
I have a further issues, I have noticed a lot of 'investtigation' needed. This is because there are employees who have had multiple entries, e.g. on sheet 1 same employee number with more hours.

I have added this on new rows on both worksheets and highlighted yellow. I would be most grateful if you could add some formula to be able to rectify this problem.

Many thanks in advance

[#10]

Re:there are employees who have had multiple entries, e.g. on sheet 1 same employee number with more hours.
I guess the question will be intuitively clear if you share a sample file.

[#11]

@peiyezhu

You make a very valid point. Please now see attached updated sample workbook.

Many thanks in advance

[#12]

@Robert1290 

 c2+c6=100

and

c12+c6=100?

[#13]

That's right, but they are on different invoices, as they could be in the same period, but different weeks. (weeks not shown).

[#14]

https://club.excelhome.net/forum.php?mod=viewthread&tid=1639955&mobile=

I guess this may need number combination or make up.
In another words,figure out which several specific numbers/hours from sheet2 sum up should equal to the one number of hours in Sheet1.

Not just a format transform from sheet2 to Sheet1.
Before the transformation,It is some kind of algrithym of number combination firstly.

[#15]

@Robert1290 

=IF(SUMIFS(Sheet2!$C$2:$C$12,Sheet2!$A$2:$A$12,Sheet1!$A2,Sheet2!$B$2:$B$12,Sheet1!$B2,Sheet2!$E$2:$E$12,Sheet1!$H2,Sheet2!$E$2:$E$12,Sheet1!$H2)<>Sheet1!$C2,"Investigate",IFERROR(INDEX(Sheet2!$D$2:$D$12,SMALL(IF((Sheet2!$A$2:$A$12=Sheet1!$A2)*(Sheet2!$B$2:$B$12=Sheet1!$B2)*(Sheet2!$E$2:$E$12=Sheet1!$H2),ROW(Sheet2!$D$1:$D$11)),COLUMN(Sheet1!A1))),""))

With an additional criteria such as weeks you can distinguish employee numbers and periods. Then you can try this formula. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel 2021.