Conditional concatination

[CasualDabbler]

I have rows of orders with the important info in the following columns
Col B Customer ID [0002]
Col C Order Date [28/11/2018]
Col P Week Index Week commencing in (yymmdd) [181126]

for example, Customer id 0002

0002, 28/11/2018, 181126 (Wed)
0002, 01/12/2018, 181126 (Sat)
Against each of these rows (Lets say col Q) I want to return "__W__S" - The "Pattern" of orders that week (181126)

0002, 05/12/2018, 181203 (Wed)
0002, 09/12/2018, 181203 (Fri)
Against each of these rows (Lets say col Q) I want to return "__W_F_" - The "Pattern" of orders that week (181203)

I have thousands of order rows where I need to return the "Order Pattern" grouped by Week

Would prefer if this can be a formula on the source row of data as opposed to a seperate pivot or anything like that

Thanks

 

[mole999]

this is part of it in Q

=B1& " "&TEXT(C1,"dd/mm/yyyy")&" "&TEXT(P1,"yymmdd").

In P =C1-WEEKDAY(C1,2)+1 for the first Monday of the week


I'm not understanding your constructs

 

[Eric W]

Perhaps something like this:

<
/tr>

	B	C	P	Q	R	S
Cust ID	Order Date	Week	Pattern
___W__S
___W__S
S__W___
S__W___

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]11/28/2018[/TD]
[TD="align: right"]181126[/TD]
[TD="align: right"]0001001[/TD]

[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]12/1/2018[/TD]
[TD="align: right"]181126[/TD]
[TD="align: right"]0001001[/TD]

[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]12/5/2018[/TD]
[TD="align: right"]181203[/TD]
[TD="align: right"]1001000[/TD]

[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]12/9/2018[/TD]
[TD="align: right"]181203[/TD]
[TD="align: right"]1001000[/TD]

[TD="align: right"][/TD]

</tbody>

Sheet6​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]Q2[/TH]
[TD="align: left"]{=TEXT(SUM(IF($P$2:$P$10=P2,10^(7-WEEKDAY($C$2:$C$10)))),"0000000")}[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]R2[/TH]
[TD="align: left"]{=CONCAT(IF(MID(TEXT(SUM(IF($P$2:$P$10=P2,10^(7-WEEKDAY($C$2:$C$10)))),"0000000"),{1,2,3,4,5,6,7},1)="1",MID("SMTWHFS",{1,2,3,4,5,6,7},1),"_"))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

The formula in Q2 returns a 1/0 pattern where 1 means the day is there, 0 means it isn't. It also requires that you don't have the same date in column C more than once. If you want the actual letters, you can try the formula in R2. It requires the CONCAT function, available in Excel 365. If you don't have that, you can still create the formula, but it would be very long and/or require 7 helper cells.

Both of these are array formulas.

 

[CasualDabbler]

Thank you for the effort so far, much appreciated.
It is constructed as Eric Guessed.
The formulas work with the test data, but not when I expand it to the rest of the data.
Suspect this is due to the rest of the data having more customers, so the dates in Col "C" do appear multiple times as Eric already warned in his note

 

[Eric W]

I should have added the Cust ID check to the formulas - here are the updated versions:

=TEXT(SUM(IF($P$2:$P$10=P2,IF($B$2:$B$10=B2,10^(7-WEEKDAY($C$2:$C$10))))),"0000000")

and

=CONCAT(IF(MID(TEXT(SUM(IF($P$2:$P$10=P2,IF($B$2:$B$10=B2,10^(7-WEEKDAY($C$2:$C$10))))),"0000000"),{1,2,3,4,5,6,7},1)<>"0",MID("SMTWHFS",{1,2,3,4,5,6,7},1),"_"))

both with CSE.

Actually, the date can appear more than once per column, but preferably only once per Customer ID. Even that's not a huge problem. The first formula might return

2001000

instead of

1001000

if the Sunday date is there twice. This means that the date can occur for a given Cust ID, for a given week, up to 9 times before the carryovers start to cause problems. If you still have issues, try to post a sample where the problem occurs.

 

[CasualDabbler]

The suggested formula in col “Q” doesn’t reference the customer anywhere, which I think is the issue
It’s grouping by Col “P” (W/C Date), which appears thousands of times.

If I concat week & store id, thus creating a unique reference for each customer week and point the formula to that (CSE) it looks like it’s working
Need to go through the data and confirm this, but fingers crossed
Thanks a bunch!!

 

[CasualDabbler]

Lol totally missed that you've posted that exact solution 20 mins ago - Thanks

 

[Eric W]

Glad to help, thanks for the feedback!