Complex Excel Vlookup or Other Solutions Welcome

[hscott82]

Hello, I am trying to create a solution to point out employees on a spreadsheet that need to be researched for hours processing. When they have over 40 hours they go on a spreadsheet we have to look at their worked location and hours columns.

I was thinking either a Vlookup or maybe even a Conditional Formatting that will highlight the Differences.

First, I need to look at their Base Rate (Column J) on Sheet 1, compare to their REG1 (out of other codes) Column N on Sheet 2 to see if they have a higher Rate paid in Column P; their could be multiple values.

Next I need to compare their Home Location (Column E) on Sheet 1 to their worked location on Sheet 2 (Column U).

I have images if it helps!

 

[alansidman]

Post some samples for a clearer understanding.

 

[hscott82]

We need to compare the Work Site Name (U) to the Home Loc Name (J) and compare the Base Rate (H) to the Rate (P) and highlight if there is a difference on the Research page. We spend a LOT of time manually going through 100+ lines of employees to see if there is a difference and there may be 20-30/100 that need a change noted on the Research tab in Columns J and K.

 

[DanteAmor]

You can clarify the following:
- I take the "099-houston office" data from column U of the "Origin" sheet
- I look for the data in the "search" sheet in column J, but there is this data: "099-CREW"
- So what I should look for is the data "099", is it always the first 3 digit?
- I compare the data "9.5" of column H of the "origin" sheet against the data of column "I" of the "search" sheet, but there I find this "$ 9.50", it seems that on one sheet you have text and in the other a number, is that so?
- Do something with column J of the "origin" sheet against column D of the "search" sheet?
- Which sheet should be marked?
- In column K of the "search" sheet should something be put?

Finally, can this be done with a macro?

 

[hscott82]

Hello Dante,

-Yes it is always the first 3 digits.
-Yes I believe the 9.50 on each sheet is formatted differently, we could always change the format if needed.
-No we don't need to compare J or K from Research, those are added manually by hand AFTER our researching is done. Mostly just looking at everything to the left of J/K.
-Research sheet should highlight when there is a difference that needs to be researched.
-Again, J/K are just manual columns. Nothing needs to be auto-populated there.

 

[DanteAmor]

Try this formula to put a result in column L

Book1
	I	J	K	L
1	BASE	WORK		RESULT
2	9.5	099-CREW		Same
3	5.55	470-MANAGER		Does not exist in Orig
4	7.2	022-CREW		Difference
Research

Cell Formulas
Range	Formula
L2:L4	L2	=IFERROR(IF(I2=INDEX(Orig!$H$2:$H$20,MATCH(LEFT(J2,3)&"*",Orig!$U$2:$U$20,0)),"Same","Difference"),"Does not exist in Orig")

 

[hscott82]

It is having some problems, I am wondering if its because the Orig sheet where it looks at is a huge file of 8,000+ employees so it has to look through all of those. It may need to look at the SW# (the employee #) to reference and find the employee on the Orig sheet?

This is what the whole top of that Research sheet looks like, there are more rows on the right and above.

Thank you so much for all your help, I can see where this is going and its so amazing!

 

[hscott82]

This is how it looks before any work is done, J/K are blank.

 

[DanteAmor]

So I did not understand what to look for and where to look.

"We need to compare the Work Site Name (U) to the Home Loc Name (J) "

But if column J is empty, then how will it be compared?
I think you should start over with the explanation.

The images are small and I can not see the data well I suggest you use the following to put the data you have on both sheets.
You can hide the columns without relevance to properly show the examples.

Upload an excel range:
XL2BB - Excel Range to BBCode

 

[hscott82]

Ok, lets try this. Sorry if I made it more complicated than it needed to be. I just need to look up the Employee (by #) from the "Research" tab and compare Home (D) and Base (I) to the "Orig" tab Work Site Name (U)(3 digits) and Rate (P) to see if there is a difference between either?

I attached the images so they open up bigger, hopefully