Stuck on Index/Match formula

Carno

New Member
Joined
May 18, 2019
Messages
2
I'm looking for assistance with an index/match type situation where I have a transaction table containing all the unique transaction codes which I then need to append to an invoice table.

To assist with the matching, I created a unique ID in each table consisting of Account#+TransDate+TransAmt.

This works fine unless a customer makes multiple payments on the same day in the same amount. Unfortunately, this happens a lot. As you can see in the image, my formula fails in this situation because it is always returning the first match. I would like to find a way to extract and append the subsequent matches.


Here is an example of the raw invoice data as it is received (csv) before I import it into the invoice table.


With regard to the TransID's in the trans table, the number to the left of the decimal is the 'batch' number for that day's payments. The number to the right of the decimal indicates the payment's position in the batch file. If there is only one payment for the day, there will be no number to the right of the decimal. In this particular example, the four trans id's highlighted in yellow are sequential but this is not always the case. Note how Acct# 4628 has two payments on 20210226 but the trans ids are 52144.1 and 52144.12.

Any suggestions on how to handle this much appreciated! Totally open to other methods for solving it.
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,966
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
First Update Your Account Details to We Know what is Best option for you Based Excel & OS Version and Save it.
I don't have Correct formula at Cell E2, But I think You Can Replace it with This Formula (after Input Fromula Press CTRL+SHIFT+ENTER):
First correct source address if is misspelling. AND After Press CTRL+SHIFT+ENTER Drag it Down.
Excel Formula:
=INDEX(tbl_Transaction18[TransID],SMALL(IF(tbl_Transaction18[UniqueID]=[@[UniqueID (Account#+TransDate+TransAmt)]],ROW(tbl_Transaction18[UniqueID])-ROW(INDEX(tbl_Transaction18[UniqueID],1,1))+1),COUNTIF($E$2:E2,E2)))
 
Last edited:

Forum statistics

Threads
1,136,323
Messages
5,675,091
Members
419,549
Latest member
EliteBeat

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top