Find a match to a specific value and search a list in a column, return data and stop at first blank cell

[Frank J]

I'm trying to use a specific value in Column A (Table: 01-DD102 for example) to find the related values in column C using the unique Trans ID. I need to return the values in the column in the same descending order then stop when I hit a blank cell so I can find the next value in column A using the same type of value from column C to return the next set of values from that column and stop at the next blank cell, and so on.

I'll then use those values to index match the Voucher Amount, Fee Amount and Total Amount columns for each Trans ID.

Sample data

 

[Peter_SSs]

Two things may better help get an answer to your question.

• Update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)
• Investigate XL2BB for providing sample data (and expected results) to make it easier for helpers by not having to manually type out sample data to test with.

 

[Frank J]

Peter,

I'm on a work computer and not able to load any outside programs due to IT restrictions so this is the best I can do. I do use office 365 so I'm betting my excel version is the same.

 

[Peter_SSs]

I'm on a work computer and not able to load any outside programs due to IT restrictions ...

You could at least copy/paste from Excel to your post then. That would still save a mass of typing that most helpers are not keen to do.

Example
It may not be pretty, but at least it can be copied.

Date	ID
19/06/2020	1234567890888
19/06/2020​	1234567890777​
20/06/2020​	1234567890241​
20/06/2020​	1234567890242​
20/06/2020​	9876543210235​

 

[Frank J]

Here is what I get

Table: 01-BP106
Batch ID	Time	Trans ID	Validation	Approved	Voucher Amount	Fee Amount	Total Amount
3432​	2020-11-08 19:33:43	2242​	60-3011-1568-3128-9858	yes	$100.00​	$6.00​	$106.00​
				Totals:	$100.00​	$6.00​	$106.00​
Table: 01-CC101
Batch ID	Time	Trans ID	Validation	Approved	Voucher Amount	Fee Amount	Total Amount
3251​	2020-11-02 21:55:52	2155​	60-1479-8261-8350-8725	yes	$1,000.00​	$24.00​	$1,024.00​
				Totals:	$1,000.00​	$24.00​	$1,024.00​
Table: 01-DD102
Batch ID	Time	Trans ID	Validation	Approved	Voucher Amount	Fee Amount	Total Amount
3405​	2020-11-07 23:19:05	2220​	60-1165-0631-9857-0069	yes	$50.00​	$5.00​	$55.00​
3375​	2020-11-06 10:45:24	2197​	60-5923-9315-2586-1215	yes	$50.00​	$5.00​	$55.00​
3345​	2020-11-06 00:54:25	2196​	60-3071-1438-1355-1027	yes	$100.00​	$6.00​	$106.00​
3255​	2020-11-02 23:30:41	2159​	60-7434-2887-5575-2807	yes	$80.00​	$5.60​	$85.60​
				Totals:	$280.00​	$21.60​	$301.60​
Table: 01-RT103
Batch ID	Time	Trans ID	Validation	Approved	Voucher Amount	Fee Amount	Total Amount
3433​	2020-11-08 09:07:39	2235​	60-5868-1263-5015-0545	yes	$40.00​	$4.80​	$44.80​
3403​	2020-11-08 02:09:37	2233​	60-1942-9214-5206-5575	yes	$200.00​	$8.00​	$208.00​
3373​	2020-11-06 10:51:02	2199​	60-3768-2917-4676-5418	yes	$50.00​	$5.00​	$55.00​
3343​	2020-11-05 20:52:40	2192​	60-9536-9783-8506-2438	yes	$300.00​	$10.00​	$310.00​
3343​	2020-11-05 20:49:44	2190​	60-1582-2516-7470-7083	yes	$500.00​	$14.00​	$514.00​
3343​	2020-11-05 20:44:40	2189​	60-3728-7920-9256-8863	yes	$800.00​	$20.00​	$820.00​
3343​	2020-11-05 20:39:34	2188​	60-9589-0670-2601-4576	yes	$800.00​	$20.00​	$820.00​
3343​	2020-11-05 20:31:54	2187​	60-7318-9531-6224-2840	yes	$800.00​	$20.00​	$820.00​
3343​	2020-11-05 20:22:56	2186​	60-9654-6205-1090-4251	yes	$800.00​	$20.00​	$820.00​
3343​	2020-11-05 20:12:55	2185​	60-5960-6844-1169-3655	yes	$800.00​	$20.00​	$820.00​
3313​	2020-11-04 22:06:31	2181​	60-8384-0784-9679-7246	yes	$60.00​	$5.20​	$65.20​
				Totals:	$5,150.00​	$147.00​	$5,297.00​

 

[Peter_SSs]

Thanks.

Assuming that you have the LET function in your Excxel 365 version try this formula. You only need to enter it in the first cell and the other results should automatically 'spill' into the other required rows.

Frank J.xlsm
	A	B	C
1
2	Table: 01-BP106
3	Batch ID	Time	Trans ID
4	3432	8/11/2020 19:33	2242
5
6
7
8	Table: 01-CC101
9	Batch ID	Time	Trans ID
10	3251	2/11/2020 21:55	2155
11
12
13
14	Table: 01-DD102
15	Batch ID	Time	Trans ID
16	3405	7/11/2020 23:19	2220
17	3375	6/11/2020 10:45	2197
18	3345	6/11/2020 0:54	2196
19	3255	2/11/2020 23:30	2159
20
21
22
23	Table: 01-RT103
24	Batch ID	Time	Trans ID
25	3433	8/11/2020 9:07	2235
26	3403	8/11/2020 2:09	2233
27	3373	6/11/2020 10:51	2199
28	3343	5/11/2020 20:52	2192
29	3343	5/11/2020 20:49	2190
30	3343	5/11/2020 20:44	2189
31	3343	5/11/2020 20:39	2188
32	3343	5/11/2020 20:31	2187
33	3343	5/11/2020 20:22	2186
34	3343	5/11/2020 20:12	2185
35	3313	4/11/2020 22:06	2181
36
37
38
39
40	Table: 01-DD102		2220
41			2197
42			2196
43			2159
44
45
Sheet1

Cell Formulas
Range	Formula
C40:C43	C40	=LET(fr,MATCH(A40,A1:A37,0)+2,INDEX(C1:C37,SEQUENCE(MATCH(TRUE,INDEX(C1:C37,fr):C37="",0)-1,,fr)))
Dynamic array formulas.

If you do not have the LET function but do have the SEQUENCE function then use this instead

=INDEX(C1:C37,SEQUENCE(MATCH(TRUE,INDEX(C1:C37,MATCH(A40,A1:A37,0)+2):C37="",0)-1,,MATCH(A40,A1:A37,0)+2))

 

[Frank J]

I do not seem to have the LET function but do have the Sequence function, when i place the formula into the cell I got the first Trans ID number but nothing else populated. Is the whole sheet supposed to spill over into the cells? To be honest I can get the raw data to a spreadsheet but I need to be able to sum up the 3 different fee amounts for each table. I need a formula to look for each table and then pull the summary of the fees to the corresponding columns.

This was done manually but needs to be automated as it will be run constantly and I can't keep doing it manually, how do I get it to recognize when a specific table's transactions end and then to sum it up?

This was my original question.

Table ID	Month	Monthly Voucher Amount	Monthly Fee Amount	Monthly Total Amount	# of Transactions
BJ103	Mar-20	$2,210.00​	$96.20​	$2,306.20​	38
BJ103	Feb-20	$2,820.00​	$152.40​	$2,972.40​
BJ103	Jan-20	$15.00​	$4.30​	$19.30​

 

[Peter_SSs]

when i place the formula into the cell I got the first Trans ID number but nothing else populated. Is the whole sheet supposed to spill over into the cells?

Not the whole sheet but the Trans ID numbers for the table of interest should spill down the column. If you start with a fresh blank worksheet and copy my sheet by clicking the BB2XL button at the top left of my sheet ..

.. and then go to cell A1 of that fresh sheet and paste, what do you see in cells C40:C43?

 

[Frank J]

Ok that works using the 2nd formula without LET function.

Correct me if I'm wrong but if I make a new sheet and pull the raw data in from the exported CSV file this'll read it and format it for me. I can save new data over the old and open it, have it read by this new sheet and use your formula to pull in the new data for me on a continuous basis.

 

[Peter_SSs]

Ok that works using the 2nd formula ..

One step in the right direction at least.

.. if I make a new sheet and pull the raw data in from the exported CSV file this'll read it and format it for me. I can save new data over the old and open it, have it read by this new sheet and use your formula to pull in the new data for me on a continuous basis.

Not sure that I fully understand. Are you saying that this formula would be on another sheet to where the data is and where the new data will be replacing the original data?
So data might continually get put into Sheet1 and this formula might be in Sheet2?