# 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?

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,MATCH
(A2&B2&C2,Sheet1!E:E,0)))

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

Hope this helps.

Pete

A

#### Ashish Mathur

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

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\$1414),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

many Thanks

A

#### Ashish Mathur

Hi,

Did you Ctrl+Shift+Enter

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

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\$1414),INDEX(\$E\$5:\$E\$9,MATCH(1,INDEX((\$B\$5:\$B\$9=B15)*(\$C\$5:\$C\$9=C15),,),0),1),"")

A

#### Ashish Mathur

Hi,

Sorry - the Ctrl+Shift+Enter is not required

1. B44 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

A

#### Ashish Mathur

Check once again - it should fill all the cells

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

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

that the file size is small - mail only the relevant portion

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com