#1
|
|||
|
|||
Searching for a "phrase" in a cell.
This question is hard to word. I have 2 sheets, the first is how much money we think we spent, the second is how much money we actually spent. There are over 100 rows in each sheet. Every day, I must match up each sheet to verify that billing was done correctly.
Items in the first sheet list the code for the item only, example: - Column A lists the common codes "1425" - Column B lists amount spent on item "8.67" Items in the second sheet list more information, example: - Column A lists; Common code, Name, Bar code "1425 Fruit Loops 13467984" - column B lists amount spent on item "8.67" When all of the items match up, it's easy enough to simply filter and have a formula to add/subtract from Column B. the issue is when one of the sheets is missing an item or has an extra one. Is there a formula that simply searches a column for the first four digits listed in column A, then add/subtract from the next cell over in column B? |
#2
|
|||
|
|||
I think a left with an index and match could work
can you mock up an example so we can test it? |
#3
|
|||
|
|||
Quote:
The only thing making this difficult are missing/added items in either sheet. You will notice an item on each sheet that is unlisted in the other. |
#4
|
|||
|
|||
Something like this?
|
#5
|
|||
|
|||
Very close, but while the Cross match sheet shows "1444" not accounted for in billing, it doesn't show "1389 Apple Loops 43456789" being billed against. Is there a formula that would point out items not accounted for in the Projection sheet?
By the way, what you just provided is so helpful. Even if this is as for we go, thank you. |
#6
|
|||
|
|||
are you using excel 2016 or office 365?
|
#7
|
||||
|
||||
Like the attached?
|
#8
|
|||
|
|||
Nice work p45cal - good solution. Must admit that Power Query is still witch craft to me!
|
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Searching for any instances of immediate word repetition (eg "the the") | bertietheblue | Word | 6 | 02-05-2018 06:30 PM |
Updating "Parent Phrase" to change all Child Phrases - (References, Fields, Headings?) | tclass117 | Word | 1 | 11-18-2016 10:14 PM |
'Linking' entered information to other "cells" from an original "cell" in MS Word | Wade | Word | 6 | 09-03-2012 05:22 PM |
How to choose a "List" for certain "Heading" from "Modify" tool? | Jamal NUMAN | Word | 2 | 07-03-2011 03:11 AM |
How can I paste cell "A1" contents to cell "B1" if cell "A1" is not blank? | Learner7 | Excel | 1 | 04-25-2011 04:39 AM |