Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 10-26-2020, 02:27 AM
SoMany SoMany is offline Searching for a "phrase" in a cell. Windows 7 64bit Searching for a "phrase" in a cell. Office 2016
Advanced Beginner
Searching for a "phrase" in a cell.
 
Join Date: Oct 2016
Posts: 51
SoMany is on a distinguished road
Default 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?
Reply With Quote
  #2  
Old 10-26-2020, 03:21 AM
Purfleet Purfleet is offline Searching for a "phrase" in a cell. Windows 10 Searching for a "phrase" in a cell. Office 2019
Expert
 
Join Date: Jun 2020
Location: Essex
Posts: 345
Purfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to behold
Default

I think a left with an index and match could work

can you mock up an example so we can test it?
Reply With Quote
  #3  
Old 10-26-2020, 04:38 AM
SoMany SoMany is offline Searching for a "phrase" in a cell. Windows 7 64bit Searching for a "phrase" in a cell. Office 2016
Advanced Beginner
Searching for a "phrase" in a cell.
 
Join Date: Oct 2016
Posts: 51
SoMany is on a distinguished road
Default

Quote:
Originally Posted by Purfleet View Post
I think a left with an index and match could work

can you mock up an example so we can test it?
Here is the mock up, but I ran into another issue.
  • The third sheet "Cross match" must display the "Common Codes" listed in the first sheet. And these codes aren't always the same of course.
  • The third sheet must highlight in some way the unlisted items in either sheet.

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.
Attached Files
File Type: xlsx Cross match.xlsx (14.0 KB, 9 views)
Reply With Quote
  #4  
Old 10-26-2020, 05:12 AM
Purfleet Purfleet is offline Searching for a "phrase" in a cell. Windows 10 Searching for a "phrase" in a cell. Office 2019
Expert
 
Join Date: Jun 2020
Location: Essex
Posts: 345
Purfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to behold
Default

Something like this?
Attached Files
File Type: xlsx Cross match_Purfleet.xlsx (17.2 KB, 8 views)
Reply With Quote
  #5  
Old 10-26-2020, 06:32 AM
SoMany SoMany is offline Searching for a "phrase" in a cell. Windows 7 64bit Searching for a "phrase" in a cell. Office 2016
Advanced Beginner
Searching for a "phrase" in a cell.
 
Join Date: Oct 2016
Posts: 51
SoMany is on a distinguished road
Default

Quote:
Originally Posted by Purfleet View Post
Something like this?
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.
Reply With Quote
  #6  
Old 10-26-2020, 08:08 AM
Purfleet Purfleet is offline Searching for a "phrase" in a cell. Windows 10 Searching for a "phrase" in a cell. Office 2019
Expert
 
Join Date: Jun 2020
Location: Essex
Posts: 345
Purfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to behold
Default

are you using excel 2016 or office 365?
Reply With Quote
  #7  
Old 10-26-2020, 04:02 PM
p45cal's Avatar
p45cal p45cal is online now Searching for a "phrase" in a cell. Windows 10 Searching for a "phrase" in a cell. Office 2019
Expert
 
Join Date: Apr 2014
Posts: 863
p45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant future
Default

Like the attached?
Attached Files
File Type: xlsx msOfficeForums45862Cross match.xlsx (25.7 KB, 6 views)
Reply With Quote
  #8  
Old 10-26-2020, 10:37 PM
Purfleet Purfleet is offline Searching for a "phrase" in a cell. Windows 10 Searching for a "phrase" in a cell. Office 2019
Expert
 
Join Date: Jun 2020
Location: Essex
Posts: 345
Purfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to behold
Default

Nice work p45cal - good solution. Must admit that Power Query is still witch craft to me!
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Searching for a "phrase" in a cell. Searching for any instances of immediate word repetition (eg "the the") bertietheblue Word 6 02-05-2018 06:30 PM
Searching for a "phrase" in a cell. 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
Searching for a "phrase" in a cell. How to choose a "List" for certain "Heading" from "Modify" tool? Jamal NUMAN Word 2 07-03-2011 03:11 AM
Searching for a "phrase" in a cell. 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

Other Forums: Access Forums

All times are GMT -7. The time now is 05:24 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft