still having problems filter?

edsjr · Mar 29th, 2002, 03:31 PM

I have 2 files, one with about 27000 lines and the other with 30,000. Each line is different but the one with 30,000 has all of the ones that the one with 27,000 plus an extra 3000. I need to match up the similar lines between the 2 files. Each file contains the same 4 columns that are unique to the line they are on. I have tried filtering without luck but I am new with that function so I am not sure I did it correctly.

anyone have any ideas?

thank you, this is extremely important

Ed.

lenze · Mar 29th, 2002, 03:43 PM

Not sure i understand what you are asking? Do you want to find the 3000 extra lines or the 27000 that are the same? Do you wish to delete the extras, or the duplicates?

edsjr · Mar 29th, 2002, 07:23 PM

I want to find the extra 3000 lines and seperate them out. More importantly I want to put like cells next to each other, which would also point out the extra 3000.

Each file has 4 columns, they represent the same things in both files. One is a manufacturer, one is a part number, one is the unit of measure and one is the price. I want to see which part numbers are in both files and compare the prices between the 2 files.

hope this clears it up and i really hope you have a solution.

daleyman · Mar 29th, 2002, 07:36 PM

I think VLOOKUP is going to save your day.

Can be used for both of the suggested solutions.

Quick tutorial then, you have:

sheet1: 30K rows
sheet2: 27K rows, all of which in sheet1

ok, first select your dataset on sheet2 and give it a name, eg."source". This can be done by typing the name into the namebox (located to the left of the formula bar). Ensure that the first column that you select is the key info that matches to the other sheet, like the part number, other columns must be to the right of this info and would include unit of measure or price.

in your sheet1, lets suppose your key info (the part number) is in column A and you want to fill the result into column F, in cell F2 put the formula:

=VLOOKUP(A2,source,2,false)

this means, lookup value from [A2] of this sheet in the first column of the named range [source] and return the value from the second column of the named range. False means basically, only identify for exact matches (ie. don't lie to me!!!).

Where there is no match this formula will return an error, which you can suppress using TYPE...

=if(TYPE(VLOOKUP(A2,source,2,false))=16,"not found",VLOOKUP(A2,source,2,false))

...hope this does it for you, play with it anyway, D.

daleyman · Mar 29th, 2002, 07:40 PM

oh, i almost forgot, once you are happy with the formula, use the fill button (on the bottom right hand corner of the cell when selected) to fill the formula down. If you have thousands of entries just double-click the fill button to fill to the bottom of the adjoining list.

also, vlookups are quite intensive on resources, so if it is a once off lookup that does not need to reference the source every time a change is made, then i suggest you get into the habit of copying your equations to values .... to do this select the whole column, then by using the right mouse button on the edge of the selection drag the selection away and drop it back where you dragged it from, then release the right mouse button and select "copy to values".

based on your example, you could do this whole process both ways, so that each sheet has reference to which items are included and which missing from the other sheet.

daleyman · Mar 29th, 2002, 07:41 PM

ouch, sorry, one last point, you might want to copy the two worksheets to the same workbook (the same file) if you want to make the referencing easier.

edsjr · Mar 29th, 2002, 09:00 PM

that didnt seem to do it, I cant get it to work at all. I even tried to rig it so it was definately there and still nothing. I copied the 27000 file to the right of the 30000 file and then put the command in.

thanks
Ed.

daleyman · Mar 29th, 2002, 09:02 PM

ok, step one is to copy the sheets into the same workbook. is that ok?

then name the range from which lookup equations will reference... ok?

at what point does it not work?

daleyman · Mar 29th, 2002, 09:12 PM

is there a common key on which to do the lookup or is the unique-ness a product of several fields combined?

daleyman · Mar 29th, 2002, 10:03 PM

i've jsut seen your earlier postings on this subject, i'm pretty sure it's vlookup you need, if you like send me the workbook or a sample of the data and i'll send you an example...

daley_123@hotmail.com

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 29th, 2002, 03:31 PM	#1
edsjr New Member Join Date: Mar 2002 Location: Illinois Posts: 19	I have 2 files, one with about 27000 lines and the other with 30,000. Each line is different but the one with 30,000 has all of the ones that the one with 27,000 plus an extra 3000. I need to match up the similar lines between the 2 files. Each file contains the same 4 columns that are unique to the line they are on. I have tried filtering without luck but I am new with that function so I am not sure I did it correctly. anyone have any ideas? thank you, this is extremely important Ed.

Mar 29th, 2002, 03:43 PM	#2
lenze MrExcel MVP Join Date: Feb 2002 Location: Helena, MT Posts: 13,690	Not sure i understand what you are asking? Do you want to find the 3000 extra lines or the 27000 that are the same? Do you wish to delete the extras, or the duplicates?

Mar 29th, 2002, 07:23 PM	#3
edsjr New Member Join Date: Mar 2002 Location: Illinois Posts: 19	I want to find the extra 3000 lines and seperate them out. More importantly I want to put like cells next to each other, which would also point out the extra 3000. Each file has 4 columns, they represent the same things in both files. One is a manufacturer, one is a part number, one is the unit of measure and one is the price. I want to see which part numbers are in both files and compare the prices between the 2 files. hope this clears it up and i really hope you have a solution.

Mar 29th, 2002, 07:36 PM	#4
daleyman Board Regular Join Date: Mar 2002 Location: London, UK Posts: 167	I think VLOOKUP is going to save your day. Can be used for both of the suggested solutions. Quick tutorial then, you have: sheet1: 30K rows sheet2: 27K rows, all of which in sheet1 ok, first select your dataset on sheet2 and give it a name, eg."source". This can be done by typing the name into the namebox (located to the left of the formula bar). Ensure that the first column that you select is the key info that matches to the other sheet, like the part number, other columns must be to the right of this info and would include unit of measure or price. in your sheet1, lets suppose your key info (the part number) is in column A and you want to fill the result into column F, in cell F2 put the formula: =VLOOKUP(A2,source,2,false) this means, lookup value from [A2] of this sheet in the first column of the named range [source] and return the value from the second column of the named range. False means basically, only identify for exact matches (ie. don't lie to me!!!). Where there is no match this formula will return an error, which you can suppress using TYPE... =if(TYPE(VLOOKUP(A2,source,2,false))=16,"not found",VLOOKUP(A2,source,2,false)) ...hope this does it for you, play with it anyway, D.

Mar 29th, 2002, 07:40 PM	#5
daleyman Board Regular Join Date: Mar 2002 Location: London, UK Posts: 167	oh, i almost forgot, once you are happy with the formula, use the fill button (on the bottom right hand corner of the cell when selected) to fill the formula down. If you have thousands of entries just double-click the fill button to fill to the bottom of the adjoining list. also, vlookups are quite intensive on resources, so if it is a once off lookup that does not need to reference the source every time a change is made, then i suggest you get into the habit of copying your equations to values .... to do this select the whole column, then by using the right mouse button on the edge of the selection drag the selection away and drop it back where you dragged it from, then release the right mouse button and select "copy to values". based on your example, you could do this whole process both ways, so that each sheet has reference to which items are included and which missing from the other sheet.

Mar 29th, 2002, 07:41 PM	#6
daleyman Board Regular Join Date: Mar 2002 Location: London, UK Posts: 167	ouch, sorry, one last point, you might want to copy the two worksheets to the same workbook (the same file) if you want to make the referencing easier.

Mar 29th, 2002, 09:00 PM	#7
edsjr New Member Join Date: Mar 2002 Location: Illinois Posts: 19	that didnt seem to do it, I cant get it to work at all. I even tried to rig it so it was definately there and still nothing. I copied the 27000 file to the right of the 30000 file and then put the command in. thanks Ed.

Mar 29th, 2002, 09:02 PM	#8
daleyman Board Regular Join Date: Mar 2002 Location: London, UK Posts: 167	ok, step one is to copy the sheets into the same workbook. is that ok? then name the range from which lookup equations will reference... ok? at what point does it not work?

Mar 29th, 2002, 09:12 PM	#9
daleyman Board Regular Join Date: Mar 2002 Location: London, UK Posts: 167	is there a common key on which to do the lookup or is the unique-ness a product of several fields combined?

Mar 29th, 2002, 10:03 PM	#10
daleyman Board Regular Join Date: Mar 2002 Location: London, UK Posts: 167	i've jsut seen your earlier postings on this subject, i'm pretty sure it's vlookup you need, if you like send me the workbook or a sample of the data and i'll send you an example... daley_123@hotmail.com