Strange Famous

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

__________________
"Do not tell lies, and do not do what you hate,
for all things are plain in the sight of Heaven. For nothing
hidden will not become manifest, and nothing covered will remain
without being uncovered."

The Gospel of Thomas

LordEden

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.

__________________
Vice-President of the CinnamonGirl Fan Club - The Meat of the Zombiesquirrel and CinnamonGirl Sandwich

Strange Famous

Hi

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

Attached Images

Untitled.jpg (96.5 KB, 28 views)

__________________
"Do not tell lies, and do not do what you hate,
for all things are plain in the sight of Heaven. For nothing
hidden will not become manifest, and nothing covered will remain
without being uncovered."

The Gospel of Thomas

dlish

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

__________________
An injustice anywhere, is an injustice everywhere

I always sign my facebook comments with ()()===========(}. Does that make me gay?
- Filthy

Strange Famous

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.

__________________
"Do not tell lies, and do not do what you hate,
for all things are plain in the sight of Heaven. For nothing
hidden will not become manifest, and nothing covered will remain
without being uncovered."

The Gospel of Thomas

GreyWolf

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.

__________________
The secret to great marksmanship is deciding what the target was AFTER you've shot.

Strange Famous

Yep... thanks for that.

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

__________________
"Do not tell lies, and do not do what you hate,
for all things are plain in the sight of Heaven. For nothing
hidden will not become manifest, and nothing covered will remain
without being uncovered."

The Gospel of Thomas