-2

I have two excel files that I need to cross reference amounts in.

The first sheet looks like the below:

enter image description here

what I need to do is find any amount that are contained on sheet 2 and the month they fall into.

Sheet 2 looks like below:

enter image description here

for example on sheet 1 I have 56.49 in column C for reference AK1080117 in column A and this shows as Person 8 on sheet 2.

I can see this is correct as on sheet one it has a transaction date of 08-jan and on sheet 2 is in the column JAN.

There is no same ref that can be used between sheet 1 and 2 as sheet 1 has Reference and sheet 2 has Name.

Can anyone advise the best way to do this.

The complete sheets are hundreds of lines long.

Many thanks,

Community
  • 1
  • 1
PIPRON79
  • 131
  • 1
  • 9
  • Without a matching index column to go between the data i.e. sheets there is no guaranteed way... – Solar Mike May 13 '18 at 10:38
  • Use an index/aggregate/countif to attempt to match amounts with amounts for the same month, You may not completely match 32 for exact 32 but you will match 32 with some 32. –  May 13 '18 at 10:39
  • Once there are two amounts that are the same and on the same day....... 6 instances of 32.00 etc – Solar Mike May 13 '18 at 10:40
  • I agree with Solar Mike's sentiment here. There is no method which guarantees correct results, due to the risk of duplicate amounts on the same day. You really shouldn't proceed with this unless you're willing to take the risk of making systematic errors. Provided it is possible, the best way to proceed then would be to upgrade the source material, so that cross-referencing can be done consistently between the sheets. – Miqi180 May 13 '18 at 11:26

2 Answers2

2
  • Note: Make sure your data has unique values AND it is not exceding the year 2017

If so, I have found a way to maybe do this in a few steps:

1: Add another column E to your first sheet and add this formula to second row of the column and drag it all the way down:

=SUBSTITUTE(ADDRESS(1,MONTH(B2)+1,4),"1","")

2: Now create another column F next to the freshly made one and put this formula in second row:

=MATCH(C2;INDIRECT("Sheet2!"&E2&":"&E2);0)

3: Now create a third column G and put this formula in second row to be dragged down:

=INDEX(Sheet2!A1:D15;F2;1)

4: Now you have created a cross-reference because column G will show you the person with a match.

Edit: You can combine the three formulas direclty obviously but my personal preference is to brake things down to make them easier to understand :)

=INDEX(Sheet2!A1:D15;MATCH(C2;INDIRECT("Sheet2!"&SUBSTITUTE(ADDRESS(1,MONTH(B2)+1,4),"1","")&":"&SUBSTITUTE(ADDRESS(1,MONTH(B2)+1,4),"1",""));0);1)

Good luck with it!

JvdV
  • 41,931
  • 5
  • 24
  • 46
  • You can see [all your deleted answers here](https://stackoverflow.com/search?tab=votes&q=%5bregex%5d%20score%3a1%20deleted%3ayes%20is%3aa). Btw [this has been deleted again](https://stackoverflow.com/q/65949774/548225) Please undelete. – anubhava Feb 23 '21 at 05:30
  • [Unwarranted deletions are being discussed here](https://meta.stackoverflow.com/questions/405460/what-to-do-when-one-person-tries-to-delete-every-duplicate) – anubhava Feb 23 '21 at 05:32
  • 1
    @anubhava Unfortunately it is deleted again. But I can't be bothered opening it again seeing it deleted a few hours later. Looking through the list of deleted answers that you shared it's hard to believe these add no value, specifically [this](https://stackoverflow.com/a/65548719/9758194) one. – JvdV Feb 23 '21 at 08:04
  • Thanks for your response. We don't need to undelete immediately or rather not undelete at all if we think question warrants deletion. – anubhava Feb 23 '21 at 08:14
  • Right dupe on one hand but deletion is totally uncalled for. You may please leave comment on meta post being discussed today – anubhava Feb 23 '21 at 08:18
  • 1
    @anubhava, I'll keep an eye on the discussion and if I can have a valuable addition I will comment/answer. – JvdV Feb 23 '21 at 08:20
0

You need a third table that has Reference and Name. Then you can use lookup functions or table relationships to link the data together.

Ask the source of the first table to include Name as a field.

mer_curius
  • 248
  • 2
  • 7