Conditonal Statement for Consecutive Dates

[AustinRiggs94]

Hi,

Trying to figure out how i can write a formula that will combine values based on consecutive days (See below). The issue is some of the values have generic ID's and the only way to tell that they are suppose to combined is by the dates. Is there anyway to write a formula that will combine values based on consecutive days?

Thanks everyone.

This is just an example but some data might be same/generic such as "hilton hotel" Amount "XXXXX" 

42	2018-03-15	HILTON GARDEN INN ORLA	4.26	Description Input 3
44	2018-03-16	HILTON GARDEN INN ORLA	347.63	Description Input 4
53	2018-06-24	SEEHOTEL NIEDERNBERG	245.24	Description Input 5
55	2018-06-25	SEEHOTEL NIEDERNBERG	245.24	Description Input 6
57	2018-06-26	SEEHOTEL NIEDERNBERG	245.24	Description Input 7
58	2018-06-27	SEEHOTEL NIEDERNBERG	42.35	Description Input 8
59	2018-06-27	SEEHOTEL NIEDERNBERG	90.75	Description Input 9
Input 2
Record ID	Date	Transaction		Description
180	2018-02-15	Phone*BILL PAYMENT	197.97	Description 2
184	2018-03-05	AVIS RENT-A-CAR 1,DENVER,CO,USA	205.88	Description 3
187	2018-03-16	HILTON GARDEN INN ORLA,ORLANDO,FL,USA	351.89	Description 4
202	2018-06-27	SEEHOTEL NIEDERNBERG,NIEDERNBERG,DEU,DEU	868.82	Description 5

I tried fuzzy match but it was not coming up with many or any values at all, even after playing around with it.

[Thableaus · Accepted solution]

Hi @AustinRiggs94 

You could do a conditional Running_Sum with Multi-Row Formula tool if you want to sum consecutive dates.

But I think if we knew more about your data we could suggest something better to do all the matching you need.

Cheers,

[AustinRiggs94]

@Thableaus  Attached is more of the data i will be working with. As you can you see I have various charges that accumulate to one "bill" which is what i will be matching on another report. The another input (on same Excel) has similar descriptions but different amount and the fuzzy Match wasn't matching all items (based on dates,description and amount).

Going off of what you provided you said i should be able to sum the amount based on dates per description correct? That would allow me to have the total "bill" per description? The issue is that, some bills range 1-5 days, would there be anyway to differentiate by formula? Thanks for your help, I have been stuck on this for a while.

[AustinRiggs94]

@Thableaus I think i got this working, added a second condition to your statement to include same day charges (such as meals/parking (see Book 2)). 

Looking at the data in input 2 some charges are separated some are together (See foundation Hotel Data). I will run a similar formula with this data then  fuzzy match and then it should work.

Again thank you for your help ,you've helped me on numerous occasions and im sure you will in the future again haha