Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Matching
Could people see my matching problem posted before. I have don an example to show my problem. It is attached. Thanks for the help +-------------------------------------------------------------------+ |Filename: Book1.zip | |Download: http://www.excelforum.com/attachment.php?postid=4330 | +-------------------------------------------------------------------+ -- cj21 ------------------------------------------------------------------------ cj21's Profile: http://www.excelforum.com/member.php...o&userid=25673 View this thread: http://www.excelforum.com/showthread...hreadid=509910 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Matching
Not sure what you're trying to do but I noticed that your formulae are offset by 1...meaning that the formula in B2 is trying to match A1 the formula in B3 A2 and so on. If your trying to get rid of the #N/A then use: =IF(ISNA(MATCH(A2,$C$2:$C$7,0)),"",MATCH(A2,$C$2:$ C$7,0)) HTH Jean-Guy -- pinmaster ------------------------------------------------------------------------ pinmaster's Profile: http://www.excelforum.com/member.php...fo&userid=6261 View this thread: http://www.excelforum.com/showthread...hreadid=509910 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Matching
You are right i have made an error there-it should be A2 as opposed to A1. However, for my real data set the match does not work-for some values, even though the format is correct. I can't work out why. On an exactly the same data set it works. Unfortunately the file is too big to post. Chris -- cj21 ------------------------------------------------------------------------ cj21's Profile: http://www.excelforum.com/member.php...o&userid=25673 View this thread: http://www.excelforum.com/showthread...hreadid=509910 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Matching
The way your formula is set up it will only return a value for an exact match. try: =MATCH(A2,$C$2:$C$7,1) HTH Jean-Guy -- pinmaster ------------------------------------------------------------------------ pinmaster's Profile: http://www.excelforum.com/member.php...fo&userid=6261 View this thread: http://www.excelforum.com/showthread...hreadid=509910 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Matching
But the problem is i do want a perfect match. I have 2 lists: Product Exception 01238451 01238451 01323472 01355763 01355763 02545364 02454776 The product list is greater than the exceptions list. I want to use the match formula, to show me where the exceptions are, then i can delete them (made easier by using the filter). I do not know why the match formal does not work for all of the exceptions, it does for some. -- cj21 ------------------------------------------------------------------------ cj21's Profile: http://www.excelforum.com/member.php...o&userid=25673 View this thread: http://www.excelforum.com/showthread...hreadid=509910 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Matching
I think i've found where the problem lies. My product list is 5000 rows, the first 700 product codes begin with a zero, thus the product column and the exceptions column are both formatted as text. The match formula works for the first 700 rows (products beginning with a 0) and then stops- i don't know why? I have tried changing the format but that does not work. -- cj21 ------------------------------------------------------------------------ cj21's Profile: http://www.excelforum.com/member.php...o&userid=25673 View this thread: http://www.excelforum.com/showthread...hreadid=509910 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Matching
Don't know if this will help but try: assuming all the exceptions codes are 8 characters long =MATCH(TEXT(A2,"00000000"),$C$2:$C$7,0) HTH Jean-Guy -- pinmaster ------------------------------------------------------------------------ pinmaster's Profile: http://www.excelforum.com/member.php...fo&userid=6261 View this thread: http://www.excelforum.com/showthread...hreadid=509910 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Matching
Or maybe: =MATCH(TEXT(A2,"00000000"),TEXT($C$2:$C$7,"0000000 0"),0) this is an array formula so enter using Ctrl+Shift+Enter Regards Jean-Guy -- pinmaster ------------------------------------------------------------------------ pinmaster's Profile: http://www.excelforum.com/member.php...fo&userid=6261 View this thread: http://www.excelforum.com/showthread...hreadid=509910 |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Matching
Thanks for the help, but it still does not work. I don't know what too do! As well as the Product column there are also other columns on sheet 1, one of which is price. On another sheet i have descriptive statistics formulas (e.g. mean median etc) that are linked to the prices on sheet 1. But i don't see why this should matter. I am trying to delete the exceptions so that the descriptive statistics change. Chris -- cj21 ------------------------------------------------------------------------ cj21's Profile: http://www.excelforum.com/member.php...o&userid=25673 View this thread: http://www.excelforum.com/showthread...hreadid=509910 |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Matching
This may not be an option but you could try transforming all your codes in numbers. here's how, type the number 1 in an empty cell, copy that cell next select all your codes then Edit/Paste Special and select "Multiply" hit Ok. If that doesn't work then I suggest re-posting with sample data that is not working, I'm sure some of the MVP's out there could help you. Regards Jean-Guy -- pinmaster ------------------------------------------------------------------------ pinmaster's Profile: http://www.excelforum.com/member.php...fo&userid=6261 View this thread: http://www.excelforum.com/showthread...hreadid=509910 |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Matching
Here is a short example. Note how the 3 is missing. +-------------------------------------------------------------------+ |Filename: Book1.zip | |Download: http://www.excelforum.com/attachment.php?postid=4336 | +-------------------------------------------------------------------+ -- cj21 ------------------------------------------------------------------------ cj21's Profile: http://www.excelforum.com/member.php...o&userid=25673 View this thread: http://www.excelforum.com/showthread...hreadid=509910 |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
Matching
In your sample data I was able to get a result by doing either of these 2 things. 1 - by using this array formula =MATCH(TEXT(A2,"00000000"),TEXT($C$2:$C$5,"0000000 0"),0) when entering an array formula you can not just hit enter, you need to hold down the Ctrl+Shift keys and then hit Enter. If done right curly braces {} will appear at both ends of the formula 2 - by using the last suggestion I gave you....putting 1 in a blank cell, copying it, then selecting the codes in both columns and going to Edit/Paste Special and selecting "Multiply". So if your data is constistent with the sample you gave then either of these 2 should work. If you try no. 2 then your original match formula should work. Hope this helps! Jean-Guy -- pinmaster ------------------------------------------------------------------------ pinmaster's Profile: http://www.excelforum.com/member.php...fo&userid=6261 View this thread: http://www.excelforum.com/showthread...hreadid=509910 |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
Matching
Highlight the product codes - Data - Text to Columns - Finish Highlight the exception codes - Data - Text to columns - Finish It fixed your sample, try it on your master sheet. Hope it helps. -- Morrigan ------------------------------------------------------------------------ Morrigan's Profile: http://www.excelforum.com/member.php...fo&userid=7094 View this thread: http://www.excelforum.com/showthread...hreadid=509910 |
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
Matching
This has worked very well (the text to columns approach, thankyou. However when i do this, the zeros are knocked off my product codes that begin with 01. Is there any way too stop this? -- cj21 ------------------------------------------------------------------------ cj21's Profile: http://www.excelforum.com/member.php...o&userid=25673 View this thread: http://www.excelforum.com/showthread...hreadid=509910 |
#15
Posted to microsoft.public.excel.misc
|
|||
|
|||
Matching
cj21 Wrote: This has worked very well (the text to columns approach, thankyou. However when i do this, the zeros are knocked off my product codes that begin with 01. Is there any way too stop this? Using the text-to-columns approach, I am not sure how to prevent the leading zero from disappearing in your situation. There is always someone else on this forum who can give better suggestions. Depending on your codes, maybe you can use autofilter to filter out all the codes that contains a number 10 and modify the leftover. -- Morrigan ------------------------------------------------------------------------ Morrigan's Profile: http://www.excelforum.com/member.php...fo&userid=7094 View this thread: http://www.excelforum.com/showthread...hreadid=509910 |
#16
Posted to microsoft.public.excel.misc
|
|||
|
|||
Matching
If all you codes are all the same lenght (8 characters) then maybe you could use a custom cell format of 00000000 HTH Jean-Guy -- pinmaster ------------------------------------------------------------------------ pinmaster's Profile: http://www.excelforum.com/member.php...fo&userid=6261 View this thread: http://www.excelforum.com/showthread...hreadid=509910 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need to Improve Code Copying/Pasting Between Workbooks | Excel Discussion (Misc queries) | |||
Count matching cells | Excel Worksheet Functions | |||
Selecting matching items in a column | Excel Discussion (Misc queries) | |||
Matching cells | Excel Discussion (Misc queries) | |||
Matching an Array in one Tab with an Array with another Tab | Excel Worksheet Functions |