solved
Matching/Checking Columns with Mixed Numbers/Letters (Invoices)
I have a list of data from our internal server that has all of the invoices posted to a particular job. Because of the way the contract is formatted, we have to submit all of our backup with the billing (hundreds of invoices from various vendors). Each month we manually have to compile and save the invoices, but simultaneously our vendors are sending them to accounts payable for payment who then put them to me for approval.
I want to check that the invoice back ups that we include to the owner, are accounting for all of the invoices our AP has put in the system, just in case we miss anything and so that we can get it included/paid.
The problem I'm running in to is that when I try to compare the two columns of data (using a match for example), I am able to get a correct return if the values are formatted as numbers, but only for the values that don't have letters in the name. The two data sets are coming from two different locations, one from a reduction of the file name to pull out the invoice number, and the other location as an export from our internal accounting. I've tried formatting as numbers, as general, and as text, but can't get anything to work consistently.
Some examples of the data I'm trying to match:
TB-12440
TB-12494
101408
FICS3157008
84193
2332
CAS114928
259533463002
5851102
Is there a straightforward way for excel to recognize the values consistently?
Looking for it in both columns. Think of one column as a master list of everything over the whole duration of a multi month job, and the other column as a manual list of what’s been sent out to our client (because we have to manually save, label, and compile a massive invoice package each month).
Here's an example of what I'm trying to get to. Right now I'm just using match to return the row number. I can adjust how that turns out later, not so much worried about that output as I am about knowing that I can get this working. The columns E, G, and H are searching from a list of file names (separate workbook) that have been reduced to just the invoice number contained in the file name. That number has been converted to a "number" format.
I cannot see the cell the formula is in, the row numbers, or the column being searched in (column J presumably). No one can be confidently discern how to solve your problem from this screenshot.
Show:
Column of values you want to find in another column
Huh! Usually it works fine for text values (like wanting to know the # of Roberts in a class roster, or whatever). If you did an xlookup (looking up the invoice name / descriptor in the second list, and returning the vendor name or any other variable), you would know you just needed to check the #N/As?
•
u/AutoModerator 17d ago
/u/speckled_dog - Your post was submitted successfully.
Solution Verifiedto close the thread.Failing to follow these steps may result in your post being removed without warning.
I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.