Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Matching Data in Columns
I am trying to compare data in one column with another. Let me explain. One column are manufacturer coupons put out in each sunday's paper. The second column is a list of coupons from a specifc store. I would like to create a forumla or something that will reflect when there is a match in each column. It can not be an exact match since the verbiage is not exactly the same. I will provide a short example. 4 Way Nasal Spray $1 (4-8-06)..............All Sunbeam heating pads: 20% off 8th Continent Soymilk .50 (3-5-06).........Aveeno Lip protectant: $2.99 (SS) Betty Crocker Potatoes .35 (3-5-06).......Chex Mix 4.5oz .99 Boston Lens Ca $7.99.......................Clorox (60 oz): .99 Chex Mix 4.5 oz. + .50/2 (3-5-06)...........Comtrex: $5.99 Now in this example, we have a match with the Chex mix. One is a Manufacturer coupon for .50 off when you buy two, in the other column is another Chex Mix coupon for .99 cents. These are close together but in reality both columns can have hundreds of entries and even when sorted alphabetically, not be anywhere near each other. I need something that will will identify all the matches in both columns when the columns don't exactly match, possibly by the first couple words or something. Any suggestions? Or is this possible? -- ebraun01 ------------------------------------------------------------------------ ebraun01's Profile: http://www.excelforum.com/member.php...o&userid=30340 View this thread: http://www.excelforum.com/showthread...hreadid=500035 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Matching Data in Columns
Anybody got any suggestions? -- ebraun01 ------------------------------------------------------------------------ ebraun01's Profile: http://www.excelforum.com/member.php...o&userid=30340 View this thread: http://www.excelforum.com/showthread...hreadid=500035 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Matching Data in Columns
Hi!
Maybe not exactly what you had in mind but at least it's something! You would need to use specific keywords or phrases from the 2 nd column entries and then you could search for matching keywords in the 1 st column and use conditional formatting to highlight the matches. The big drawback that I see is that this would have to done one at a time. The basic concept is the same as that used in a database except that you're not wanting to extract the data, just highlight any matches. I use this concept in my music database (Excel) except that I do extract the data. For example, I enter a keyword or phrase in a cell then the database is searched and every entry in the database that contains that keyword or phrase is extracted. Can you post a sample file? Biff "ebraun01" wrote in message ... Anybody got any suggestions? -- ebraun01 ------------------------------------------------------------------------ ebraun01's Profile: http://www.excelforum.com/member.php...o&userid=30340 View this thread: http://www.excelforum.com/showthread...hreadid=500035 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Matching Data in Columns
Bookmark
"ebraun01" wrote in message ... I am trying to compare data in one column with another. Let me explain. One column are manufacturer coupons put out in each sunday's paper. The second column is a list of coupons from a specifc store. I would like to create a forumla or something that will reflect when there is a match in each column. It can not be an exact match since the verbiage is not exactly the same. I will provide a short example. 4 Way Nasal Spray $1 (4-8-06)..............All Sunbeam heating pads: 20% off 8th Continent Soymilk .50 (3-5-06).........Aveeno Lip protectant: $2.99 (SS) Betty Crocker Potatoes .35 (3-5-06).......Chex Mix 4.5oz .99 Boston Lens Ca $7.99.......................Clorox (60 oz): .99 Chex Mix 4.5 oz. + .50/2 (3-5-06)...........Comtrex: $5.99 Now in this example, we have a match with the Chex mix. One is a Manufacturer coupon for .50 off when you buy two, in the other column is another Chex Mix coupon for .99 cents. These are close together but in reality both columns can have hundreds of entries and even when sorted alphabetically, not be anywhere near each other. I need something that will will identify all the matches in both columns when the columns don't exactly match, possibly by the first couple words or something. Any suggestions? Or is this possible? -- ebraun01 ------------------------------------------------------------------------ ebraun01's Profile: http://www.excelforum.com/member.php...o&userid=30340 View this thread: http://www.excelforum.com/showthread...hreadid=500035 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Matching Data in Columns
Sure, since coupons are good for more than a week, next week I would add that weeks coupons. Attached is an example that has The last two weeks of manufacturers coupons and CVS's coupons beginning next week. +-------------------------------------------------------------------+ |Filename: CVS Coupons.zip | |Download: http://www.excelforum.com/attachment.php?postid=4216 | +-------------------------------------------------------------------+ -- ebraun01 ------------------------------------------------------------------------ ebraun01's Profile: http://www.excelforum.com/member.php...o&userid=30340 View this thread: http://www.excelforum.com/showthread...hreadid=500035 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Matching Data in Columns
Hi!
There aren't a lot of options in doing this! Here's your file: http://s43.yousendit.com/d.aspx?id=0...82QE4CNTQ7O76K Read the notes. Biff "ebraun01" wrote in message ... Sure, since coupons are good for more than a week, next week I would add that weeks coupons. Attached is an example that has The last two weeks of manufacturers coupons and CVS's coupons beginning next week. +-------------------------------------------------------------------+ |Filename: CVS Coupons.zip | |Download: http://www.excelforum.com/attachment.php?postid=4216 | +-------------------------------------------------------------------+ -- ebraun01 ------------------------------------------------------------------------ ebraun01's Profile: http://www.excelforum.com/member.php...o&userid=30340 View this thread: http://www.excelforum.com/showthread...hreadid=500035 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Matching Data in Columns
I think this will work fine. I appreciate all that you put into it. One Question, How do I get the conditional formatting to work on column A also? I adjusted the forumula so it finds matches in that row..but the conditional formatting does not work. -- ebraun01 ------------------------------------------------------------------------ ebraun01's Profile: http://www.excelforum.com/member.php...o&userid=30340 View this thread: http://www.excelforum.com/showthread...hreadid=500035 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Matching Data in Columns
Select the range A3:A250
Goto FormatConditional Formatting Select Formula is Enter this formula in the little box: =ISNUMBER(SEARCH(LOOKUP(REPT("z",255),D:D),A3)) Click the Format button Select the style(s) desired OK out Biff "ebraun01" wrote in message ... I think this will work fine. I appreciate all that you put into it. One Question, How do I get the conditional formatting to work on column A also? I adjusted the forumula so it finds matches in that row..but the conditional formatting does not work. -- ebraun01 ------------------------------------------------------------------------ ebraun01's Profile: http://www.excelforum.com/member.php...o&userid=30340 View this thread: http://www.excelforum.com/showthread...hreadid=500035 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Matching and aranging data form two sources | Excel Worksheet Functions | |||
Data in narrow columns truncated when saving as DBF | Excel Discussion (Misc queries) | |||
Help PLEASE! Not sure what answer is: Match? Index? Other? | Excel Worksheet Functions | |||
How to take matching data from 2 columns and put in the same row? | New Users to Excel | |||
Pulling data from 1 sheet to another | Excel Worksheet Functions |