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>[TABLE="width: 0"]
<colgroup><col style="width: 100px"><col width="100"><col width="100"><col width="100"><col width="100"></colgroup><tbody>[TR]
[TD="bgcolor: #ffd966"]Line[/TD]
[TD="bgcolor: #ffd966"]FIRSTNAME[/TD]
[TD="bgcolor: #ffd966"]LASTNAME[/TD]
[TD="bgcolor: #ffd966"]ORDERID[/TD]
[TD="bgcolor: #ffd966"]DATE[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]Albert[/TD]
[TD]Albertson[/TD]
[TD="align: right"]1000[/TD]
[TD]2017-11-01[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]Albert[/TD]
[TD]Albertson[/TD]
[TD="align: right"]1500[/TD]
[TD]2017-11-01[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]Albert[/TD]
[TD]Albertson[/TD]
[TD="align: right"]1500[/TD]
[TD]2017-11-01[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD]Bob[/TD]
[TD]Bobson[/TD]
[TD="align: right"]1000[/TD]
[TD]2017-11-04[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD]Bob[/TD]
[TD]Bobson[/TD]
[TD="align: right"]1000[/TD]
[TD]2017-11-06[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD]Bob[/TD]
[TD]Bobson[/TD]
[TD="align: right"]1500[/TD]
[TD]2017-11-04[/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD]Cat[/TD]
[TD]Catson[/TD]
[TD="align: right"]1500[/TD]
[TD]2017-11-07[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 5"]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,[/TD]
[/TR]
</tbody>[/TABLE]

 

[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.

[TABLE="width: 470"]
<tbody>[TR]
[TD]FIRSTNAME[/TD]
[TD]LASTNAME[/TD]
[TD]ORDERID[/TD]
[TD]OLD DATE[/TD]
[TD]NEW DATE[/TD]
[/TR]
[TR]
[TD]Albert[/TD]
[TD]Albertson[/TD]
[TD]1500[/TD]
[TD="align: right"]01-11-2017[/TD]
[TD="align: right"]01-11-2017[/TD]
[/TR]
[TR]
[TD]Albert[/TD]
[TD]Albertson[/TD]
[TD]1500[/TD]
[TD="align: right"]01-11-2017[/TD]
[TD="align: right"]02-11-2017[/TD]
[/TR]
[TR]
[TD]Albert[/TD]
[TD]Albertson[/TD]
[TD]1500[/TD]
[TD="align: right"]02-11-2017[/TD]
[TD="align: right"]02-11-2017[/TD]
[/TR]
</tbody>[/TABLE]

 

[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:

[TABLE="width: 491"]
<tbody>[TR]
[TD]FIRST NAME[/TD]
[TD]LAST NAME[/TD]
[TD]ID[/TD]
[TD]OLD DATE[/TD]
[TD]NEW DATE[/TD]
[/TR]
[TR]
[TD]ALBERT[/TD]
[TD]ALBERTSON[/TD]
[TD="align: right"]1500[/TD]
[TD="align: right"]14-11-2017[/TD]
[TD="align: right"]14-11-2017[/TD]
[/TR]
[TR]
[TD]ALBERT[/TD]
[TD]ALBERTSON[/TD]
[TD="align: right"]1500[/TD]
[TD="align: right"]14-11-2017[/TD]
[TD="align: right"]15-11-2017[/TD]
[/TR]
[TR]
[TD]ALBERT[/TD]
[TD]ALBERTSON[/TD]
[TD="align: right"]1500[/TD]
[TD="align: right"]15-11-2017[/TD]
[TD="align: right"]15-11-2017[/TD]
[/TR]
[TR]
[TD]ALBERT[/TD]
[TD]ALBERTSON[/TD]
[TD="align: right"]1500[/TD]
[TD="align: right"]15-11-2017[/TD]
[TD="align: right"]16-11-2017[/TD]
[/TR]
[TR]
[TD]ALBERT[/TD]
[TD]ALBERTSON[/TD]
[TD="align: right"]1500[/TD]
[TD="align: right"]15-11-2017[/TD]
[TD="align: right"]17-11-2017[/TD]
[/TR]
[TR]
[TD]ALBERT[/TD]
[TD]ALBERTSON[/TD]
[TD="align: right"]1500[/TD]
[TD="align: right"]15-11-2017[/TD]
[TD="align: right"]18-11-2017[/TD]
[/TR]
[TR]
[TD]ALBERT[/TD]
[TD]ALBERTSON[/TD]
[TD="align: right"]1500[/TD]
[TD="align: right"]16-11-2017[/TD]
[TD="align: right"]16-11-2017[/TD]
[/TR]
[TR]
[TD]ALBERT[/TD]
[TD]ALBERTSON[/TD]
[TD="align: right"]1500[/TD]
[TD="align: right"]16-11-2017[/TD]
[TD="align: right"]17-11-2017[/TD]
[/TR]
</tbody>[/TABLE]

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
[Table="width:, class:grid"][tr][td] [/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/td][/tr]
[tr][td]

1​

[/td][td]FIRST NAME[/td][td]LAST NAME[/td][td]ID[/td][td]OLD DATE[/td][td]NEW DATE[/td][td]Mine[/td][/tr]

[tr][td]

2​

[/td][td]ALBERT[/td][td]ALBERTSON[/td][td]

1500​

[/td][td]

14-11-2017​

[/td][td]

14-11-2017​

[/td][td]

14-11-2017​

[/td][/tr]

[tr][td]

3​

[/td][td]ALBERT[/td][td]ALBERTSON[/td][td]

1500​

[/td][td]

14-11-2017​

[/td][td]

15-11-2017​

[/td][td]

15-11-2017​

[/td][/tr]

[tr][td]

4​

[/td][td]ALBERT[/td][td]ALBERTSON[/td][td]

1500​

[/td][td]

15-11-2017​

[/td][td]

15-11-2017​

[/td][td]

15-11-2017​

[/td][/tr]

[tr][td]

5​

[/td][td]ALBERT[/td][td]ALBERTSON[/td][td]

1500​

[/td][td]

15-11-2017​

[/td][td]

16-11-2017​

[/td][td]

16-11-2017​

[/td][/tr]

[tr][td]

6​

[/td][td]ALBERT[/td][td]ALBERTSON[/td][td]

1500​

[/td][td]

15-11-2017​

[/td][td]

17-11-2017​

[/td][td]

17-11-2017​

[/td][/tr]

[tr][td]

7​

[/td][td]ALBERT[/td][td]ALBERTSON[/td][td]

1500​

[/td][td]

15-11-2017​

[/td][td]

18-11-2017​

[/td][td]

18-11-2017​

[/td][/tr]

[tr][td]

8​

[/td][td]ALBERT[/td][td]ALBERTSON[/td][td]

1500​

[/td][td]

16-11-2017​

[/td][td]

16-11-2017​

[/td][td]

16-11-2017​

[/td][/tr]

[tr][td]

9​

[/td][td]ALBERT[/td][td]ALBERTSON[/td][td]

1500​

[/td][td]

16-11-2017​

[/td][td]

17-11-2017​

[/td][td]

17-11-2017​

[/td][/tr]
[/table]
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)