Need help with formula

[Potatomr]

Hi all,

I need help with a formula, and I'd be super happy if somebody could help me. I have explained the issue in the table below. Maybe it's possible to have a macro or similar that can "go through" the file and automatically correct the dates? What's the best solution you think? The output should be the same as below - but with the correct dates. Thanks on beforehand!

<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style><style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>

Line	FIRSTNAME	LASTNAME	ORDERID	DATE
1	Albert	Albertson	1000	2017-11-01
2	Albert	Albertson	1500	2017-11-01
3	Albert	Albertson	1500	2017-11-01
4	Bob	Bobson	1000	2017-11-04
5	Bob	Bobson	1000	2017-11-06
6	Bob	Bobson	1500	2017-11-04
7	Cat	Catson	1500	2017-11-07
Issue: If FIRSTNAME+LASTNAME has made an order with the same ORDER ID on the same DATE (like line 2 and 3), then postpone DATE by +1 for one of the lines, for example for line nbr 3. Also, if possible: Postpone DATE by +1 for Line nbr 3 UNLESS the FIRSTNAME+LASTNAME have placed an order on the +1 day with the same ORDER ID - if so then plus +2 days,

<colgroup><col style="width: 100px"><col width="100"><col width="100"><col width="100"><col width="100"></colgroup><tbody>
</tbody>

 

[Johnny C]

Use a helper column and COUNTIFS. Assuming your columns are A,B,C and D, and say you have 100 rows or less, in cell E1 put

=$D1+COUNTIFS($B$1:$B$100,$B1,$C$1:$C$100,$C1,$D$1:$D$100,$D1)-COUNTIFS($B2:$B$100,$B1,$C2:$C$100,$C1,$D2:$D$100,$D1)-1

Then copy to the bottom.

Getting the $'s right is key. It counts all the rows with the same combination in the table, then deducts how many are below so in this example it would go in col E

2017-11-04 + 0
2017-11-04 + 1

if you had 5 it would go

2017-11-04 + 0
2017-11-04 + 1
2017-11-04 + 2
2017-11-04 + 3
2017-11-04 + 4

I can't get into excel to test it right now but try it, if it doesn't work you may have to tweak it.

 

[FDibbins]

I would adjust the suggested formula slightly so it does a progressive count, rather than a full count...
=$D1+COUNTIFS($B$1:$B1,$B1,$C$1:$C1,$C1,$D$1:$D1,$D1)-1

 

[Potatomr]

Hi,

Thank you both for your answers. Unfortunately I can't get it to work. I have the data as following = A = LAST NAME, Column B = FIRST NAME, Column C = Order ID and column D = date. Although it gives me an error and I can't figure out why (just says something is wrong with the formula). Is it due to it being mixed text and numbers or something?

 

[Potatomr]

It seems I recieve and error when the ,$B1 is added, for example here: $B$1:$B1,$B1

 

[Potatomr]

Okay the problem is solved and the formula works, I needed ; instead of ,. Thanks a lot!

Although, do you have any ideas to solve the problem I mentioned as an additional problem in the first post? If Line number 2 is the same as the date it's being postponed to (line number 3, 2/11) the formula should postpone line number 2 one extra day to 3/11. What is possible is of course to do this help column two or three times based on the "new date", but if you have any ideas of how to tweak the formula to take that into consideration it would be very appreciated.

FIRSTNAME	LASTNAME	ORDERID	OLD DATE	NEW DATE
Albert	Albertson	1500	01-11-2017	01-11-2017
Albert	Albertson	1500	01-11-2017	02-11-2017
Albert	Albertson	1500	02-11-2017	02-11-2017

<tbody>
</tbody>

 

[FDibbins]

The formula I suggested in #3 should do that for you?

 

[Potatomr]

The formula I suggested in #3 should do that for you?

Unfortunately it doesn't seem like it's taking that into consideration. I tried on the following example:

FIRST NAME	LAST NAME	ID	OLD DATE	NEW DATE
ALBERT	ALBERTSON	1500	14-11-2017	14-11-2017
ALBERT	ALBERTSON	1500	14-11-2017	15-11-2017
ALBERT	ALBERTSON	1500	15-11-2017	15-11-2017
ALBERT	ALBERTSON	1500	15-11-2017	16-11-2017
ALBERT	ALBERTSON	1500	15-11-2017	17-11-2017
ALBERT	ALBERTSON	1500	15-11-2017	18-11-2017
ALBERT	ALBERTSON	1500	16-11-2017	16-11-2017
ALBERT	ALBERTSON	1500	16-11-2017	17-11-2017

<tbody>
</tbody>

What do you think?

 

[FDibbins]

My suggestion does work. From the looks of it, your "date" is not really a date, it is just text looking like a date. Below I have I converted to real dates using Text2Column

	A​	B​	C​	D​	E​	F​
1​	FIRST NAME	LAST NAME	ID	OLD DATE	NEW DATE	Mine
2​	ALBERT	ALBERTSON	1500​	14-11-2017​	14-11-2017​	14-11-2017​
3​	ALBERT	ALBERTSON	1500​	14-11-2017​	15-11-2017​	15-11-2017​
4​	ALBERT	ALBERTSON	1500​	15-11-2017​	15-11-2017​	15-11-2017​
5​	ALBERT	ALBERTSON	1500​	15-11-2017​	16-11-2017​	16-11-2017​
6​	ALBERT	ALBERTSON	1500​	15-11-2017​	17-11-2017​	17-11-2017​
7​	ALBERT	ALBERTSON	1500​	15-11-2017​	18-11-2017​	18-11-2017​
8​	ALBERT	ALBERTSON	1500​	16-11-2017​	16-11-2017​	16-11-2017​
9​	ALBERT	ALBERTSON	1500​	16-11-2017​	17-11-2017​	17-11-2017​

F2=$D2+COUNTIFS($B$2:$B2,$B2,$C$2:$C2,$C2,$D$2:$D2,$D2)-1
copied down

 

[admiral100]

Hi,

Try this :

F2 =IF(A2&B2&C2&D2=A1&B1&C1&D1,F1+1,D2)