Excel help?
 

I think you can do this, but I am not sure how!

I have two lists

One of transactions, one of cards and card owners. In list 1 there are sometimes many transactions on one card. In List 2 there is 1 line for each card and card owner

I want to basically add the Account number to the transaction file.

I used vlookup so that in List 2 I only have cards that appear in list 1, but because there are sometimes more than one transaction on a card I cant figure out how to add the account number.

(I dont have any tools other than Excel to do it)

I cant do it just by sorting as there are 11,000 transactions and 5,000 diff cards and it would take hours.

LIST 1_____LIST 2
Card 1 £100 Card 1 Acc1919
Card 1 £150 Card 2 Acc 9191
Card 2 £100 Card 3 Acc 8000
Card 3 £100 Card 4 Acc 8000
Card 4 £1
Card 4 £15
etc

I want it to say

Card 1 £100 Acc 1919
Card 1 £150 Acc 1919
Card 2 £100 Acc 9191
etc

Is this already in a spreadsheet or is it in some other file and/or format (Inside of a program or just a text file)?

What version of office are you running?

If this is in a spreadsheet already.

Is LIST 2 a column?

LIST 1_____LIST 2
Card 1 £100 Card 1 Acc1919
Card 1 £150 Card 2 Acc 9191
Card 2 £100 Card 3 Acc 8000
Card 3 £100 Card 4 Acc 8000
Card 4 £1
Card 4 £15
etc

Is there anyway you can screenshot it? I'm sure if I saw it I could figure out what you want to do.

Hi

Office 2007
Both lists are in the spreadsheets, all in columns.

i found the screenshot hard to read.

but what i think you're trying to do is link the numbers is list 1 to the account numbers in list 2. ie. if cell A2=1 then cell D2=Acc5000


is that correct?

if thats the case you can use the IF formula to link them up

List 1 has card number
List 2 has card number and account

I wasnt to have in a blank cell

"If column A (List 1 card) = Column X (List 2 card) then put here the account number that Card belongs to (which is in list 2)"

The reason I cant do it (using vlookup) is that List 1 has many transactions on a single card sometimes.

In Cell D14 put "=Vlookup(A1,$E$2:$F$5,2,true)"

This will return the account number (column 2 of List 2) into the sorted column 1. Copy that formula down to the end of the sorted List 1.

From what you've said, that should give you the results you want.

Yep... thanks for that.

My mate at work explained the same thing and it worked.