Excel Questioj
Discussion
here goes i have 2 lists of information both with a unique key field that exists in both lists.
now the 2 lists cont have the same number of rows.
list one has all items list 2 is a subset of list one with some additional data.
what i need to do is match up the two lists in excel.
i.e at the moment i have t sorted lists that go somthing like
list1 list2
1 1
2 2
3 4
4 5
5 7
6
7
8
i need to match up the 2 lists in excel so all the row s from the two files line up in one list
new list
1 1
2 2
3
4 4
5 5
6
7 7
8
with the rest of the columns from the 2 lists lining up accordingly.
can i do this in excel ????
cheers
graham
now the 2 lists cont have the same number of rows.
list one has all items list 2 is a subset of list one with some additional data.
what i need to do is match up the two lists in excel.
i.e at the moment i have t sorted lists that go somthing like
list1 list2
1 1
2 2
3 4
4 5
5 7
6
7
8
i need to match up the 2 lists in excel so all the row s from the two files line up in one list
new list
1 1
2 2
3
4 4
5 5
6
7 7
8
with the rest of the columns from the 2 lists lining up accordingly.
can i do this in excel ????
cheers
graham
Could you not use the Vertical lookup function? -[don't enter the bits in square brackets] i.e. in column A you have list one in column B =vlookup(A1[cell where unique key is in list one],$K:$M [columns containing list two and additional info],1,false) this would return a unique key value where one exists and n/a where it doesn't. In column C you could then have =vlookup(A1,$K:$M,2,false) for the additional data. Email me through my profile if this makes no sense.
oops fair point (must read code more fully) but the Lookup function has no false option. With the Vlookup if the item doesn't exist in the list then it returns #NA which can be a real pain in the ass for other formula.
It depends on what you want. If you want closest match and a result then you use TRUE and the list HAS to be in order. If you're not bothered or want #NA then use false and the list doesn't have to be in order.
One point to mention though I have found with very large lists if you use false the processing time increases. It is quicker to sort the data and then use the vlookup than to force it through every occurance. Of course with the example given its pretty irrelevent.
>> Edited by smeagol on Friday 8th August 18:40
It depends on what you want. If you want closest match and a result then you use TRUE and the list HAS to be in order. If you're not bothered or want #NA then use false and the list doesn't have to be in order.
One point to mention though I have found with very large lists if you use false the processing time increases. It is quicker to sort the data and then use the vlookup than to force it through every occurance. Of course with the example given its pretty irrelevent.
>> Edited by smeagol on Friday 8th August 18:40
vlookup workted fine using the false option on all the lookup fields.
there was only 11k items in the master list and 10k in the secondary list with 5 fields in the second list to match up.
both lists were sorted but it still takes a while to format !!!!
the file comes out at3.6mb!!
cheers Guys
G
there was only 11k items in the master list and 10k in the secondary list with 5 fields in the second list to match up.
both lists were sorted but it still takes a while to format !!!!
the file comes out at3.6mb!!
cheers Guys
G
Gassing Station | Computers, Gadgets & Stuff | Top of Page | What's New | My Stuff