Complex Excel Vlookup or Other Solutions Welcome

hscott82

New Member
Joined
Oct 30, 2019
Messages
18
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

Well-known Member
Joined
Feb 26, 2007
Messages
5,071
Office Version
2019
Platform
Windows
Post some samples for a clearer understanding.
 

hscott82

New Member
Joined
Oct 30, 2019
Messages
18
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.
Over 40's.JPG
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
8,736
Office Version
2007
Platform
Windows
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

New Member
Joined
Oct 30, 2019
Messages
18
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

Well-known Member
Joined
Dec 3, 2018
Messages
8,736
Office Version
2007
Platform
Windows
Try this formula to put a result in column L

xl2bb.xlam
IJKL
1BASEWORKRESULT
29.5099-CREW
35.55470-MANAGER
47.2022-CREW
Research
Cell Formulas
Range(s)Formula
L2:L4L2=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

New Member
Joined
Oct 30, 2019
Messages
18
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.
9846.JPG


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

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
8,736
Office Version
2007
Platform
Windows
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

New Member
Joined
Oct 30, 2019
Messages
18
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 :)
 

Attachments

Forum statistics

Threads
1,081,678
Messages
5,360,462
Members
400,586
Latest member
Minty

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top