to link information between two lists

B

Bklynhyc

Hi,

I would like to link the amounts over two lists of totals,

first list with reference.

abcd
merchant date amt ref.
1234 06/01/09 500.00 1
1233 06/02/09 100.00 2
1233 06/02/09 100.00 3
1233 06/02/09 100.00 4
1233 06/02/09 100.00 5

second list w/o reference

abcd
merchant date amt ref.
1234 06/01/09 500.00 ?
1233 06/02/09 400.00 ?

I've been using ref. number manually to link them, is there a better way?

Thanks at advance
 
P

Pete_UK

In your first list you could add a formula like this in E2:

=A2&B2&C2

and copy this down as far as your data extends. Assume this is on
Sheet1.

Then in your second list (Sheet2) you could have this formula in D2:

=IF(ISNA(MATCH(A2&B2&C2,Sheet1!E:E,0)),"",INDEX(Sheet1!D:D,MATCH
(A2&B2&C2,Sheet1!E:E,0)))

and then copy this down as far as you need to.

Hope this helps.

Pete
 
B

Bklynhyc

I'm sorry its the other way around,

abcd
merchant date amt ref.
1234 06/01/09 500.00 1
1233 06/02/09 100.00 2
1233 06/02/09 100.00 2
1233 06/02/09 100.00 2
1233 06/02/09 100.00 2

second list w/o reference

abcd
merchant date amt ref.
1234 06/01/09 500.00 1
1233 06/02/09 400.00 2
 
A

Ashish Mathur

Hi,

One among many approaches. Use the formula in cell E15. B5:E9 carries the
first three columns of data on the first list. B15, C15 and D15 carry the
merchant, data and amount ref on the second list.

=INDEX($E$5:$E$9,MATCH(1,INDEX(($B$5:$B$9=B15)*($C$5:$C$9=C15)*($D$5:$D$9=D15),,),0),1)

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com
 
B

Bklynhyc

So this is what i got , any suggestion?

=INDEX($J$2:$J$9,MATCH(1,INDEX(($G$2:$G$9=A2)*($H$2:$H$9=B2)*($I$2:$I$9=C2),,),0),1)

first list with reference.
GHIJ
merchant date amt ref.
1234 06/01/09 500.00 1
1233 06/02/09 400.00 2


second list w/o reference

abcd
merchant date amt ref.
1234 06/01/09 500.00 1
1233 06/02/09 100.00 #N/A
1233 06/02/09 100.00 #N/A
1233 06/02/09 100.00 #N/A
1233 06/02/09 100.00 #N/A
 
A

Ashish Mathur

Hi,

Try this

=IF(D15=DSUM($B$4:$D$9,D$14,$B$14:C15)-SUM($D$14:D14),INDEX($E$5:$E$9,MATCH(1,INDEX(($B$5:$B$9=B15)*($C$5:$C$9=C15),,),0),1),"")

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com
 
B

Bklynhyc

did as following but get a error.

"The formula you typed contains an error."

actually I would like the ref. the other way around

abcd
merchant date amt ref.
1234 06/01/09 500.00 ?
1233 06/02/09 100.00 ?
1233 06/02/09 100.00 ?
1233 06/02/09 100.00 ?
1233 06/02/09 100.00 ?

second list w/o reference

abcd
merchant date amt ref.
1234 06/01/09 500.00 1
1233 06/02/09 400.00 2

many thanks
 
B

Bklynhyc

Hi,

Thanks for following up, yes i have Ctrl+Shift+Enter.
Note it start at column B

1A BCDE
2
3 merchant date amt ref.
4 1234 6/1/2009 500 1
5 1233 6/2/2009 400 2
6
7
8
9
10
11
12
13 merchant date amt ref.
14 1234 6/1/2009 500 #VALUE!
15 1233 6/2/2009 100 #VALUE!
16 1233 6/2/2009 100 #VALUE!
17 1233 6/2/2009 100 #VALUE!
18 1233 6/2/2009 100 #VALUE!

=IF(D15=DSUM($B$4:$D$9,D$14,$B$14:C15)-SUM($D$14:D14),INDEX($E$5:$E$9,MATCH(1,INDEX(($B$5:$B$9=B15)*($C$5:$C$9=C15),,),0),1),"")

Please advise Thank You
 
A

Ashish Mathur

Hi,

Sorry - the Ctrl+Shift+Enter is not required

1. B4:D4 should contain Merchant, Data and Amt
2. D14 should have Amt
3. B14:C14 should have Merchant, Date

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com
 
B

Bklynhyc

Great so it does work But one problem I it only fill in the first and last
line any suggestion?

the result looks like this, I would like the other "<<?" to be filled in as
well

merchant date amt ref.
1234 6/1/2009 500 1
1233 6/2/2009 100 <<?
1233 6/2/2009 100 <<?
1233 6/2/2009 100 <<?
1233 6/2/2009 100 2


Many Thanks
 
B

Bklynhyc

Great so it work But one problem it only fill in the first and last
line any suggestion?

the result looks like this, I would like the other "<<?" to be filled in as
well (2)

merchant date amt ref.
1234 6/1/2009 500 1
1233 6/2/2009 100 <<?
1233 6/2/2009 100 <<?
1233 6/2/2009 100 <<?
1233 6/2/2009 100 2


Many Thanks
 
B

Bklynhyc

I try to add other line to see but same situation, any suggestion

merchant date amt ref.
1234 6/1/2009 500.00 1
1233 6/2/2009 400.00 2
1122 6/3/2009 100.00 3




merchant date amt ref.
1234 6/1/2009 500.00 1
1233 6/2/2009 100.00
1233 6/2/2009 100.00
1233 6/2/2009 100.00
1233 6/2/2009 100.00 2
1122 6/3/2009
1122 6/3/2009 50.00
1122 6/3/2009 0.50
1122 6/3/2009 24.50
1122 6/3/2009 25.00 3

Thank You
 
A

Ashish Mathur

Please mail the workbook to me as ask(at)ashishmathur(dot)com. Pleas ensure
that the file size is small - mail only the relevant portion

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

look for subtotal 5
how to vlookup colume with different values 2
match total with fees 2
would like to combine info. fr different location on a list 3
Sum value between two dates and copy to new cell 6
Transferring CERTAIN Data from one worksheet to another 1
macro to add numbers based on set criteria 6
Cannot verify XNPV with PV or HP-12C 4

Top