to link information between two lists

  • Thread starter Thread starter Bklynhyc
  • Start date Start date
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
 
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
 
Hi,

What should the reference number be for the second entry on the second list
I.e. should it be 2,3,4 or 5.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com
 
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
 
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
 
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
 
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
 
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
 
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
 
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
 
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
 
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
 
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
 
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
 
You must log in or register to reply here.

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
match total with fees 2
how to vlookup colume with different values 2
would like to combine info. fr different location on a list 3
Sum value between two dates and copy to new cell 6
How to use SumProduct to count reference or something else? 3
macro to add numbers based on set criteria 6
Cannot verify XNPV with PV or HP-12C 4

Back
Top