Complex Excel Vlookup or Other Solutions Welcome

hscott82

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

Some videos you may like

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

alansidman

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

hscott82

New Member
Joined
Oct 30, 2019
Messages
33
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
12,228
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
33
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
12,228
Office Version
2007
Platform
Windows
Try this formula to put a result in column L

Book1
IJKL
1BASEWORKRESULT
29.5099-CREWSame
35.55470-MANAGERDoes not exist in Orig
47.2022-CREWDifference
Research
Cell Formulas
RangeFormula
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
33
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
12,228
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
33
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

Watch MrExcel Video

Forum statistics

Threads
1,102,867
Messages
5,489,367
Members
407,686
Latest member
Chuck1960

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top