Formula for Group by Customer

[pto160]

I have a spreadsheet with customers and invoices. I would like to place zero for the first customer, then 1 for the next customer and back to zero again for the next customer. Is there a way to do this?

Book1
	A	B	C	D
1	Customer	Amount	Invoice	What I want
2	100	15	Inv-150	0
3	100	15	Inv-150	0
4	200	50	Inv-200	1
5	300	100	Inv-250	0
6	300	100	Inv-250	0
7	400	200	Inv-300	1
8	500	300	Inv-400	0
9	500	300	Inv-400	0
Sheet1

 

[alansidman]

Book8
	A	B	C	D
1	Customer	Amount	Invoice	What I want
2	100	15	Inv-150	0
3	100	15	Inv-150	0
4	200	50	Inv-200	1
5	300	100	Inv-250	0
6	300	100	Inv-250	0
7	400	200	Inv-300	1
8	500	300	Inv-400	0
9	500	300	Inv-400	0
10
11	Customer	All.Amount	All.Invoice	Custom
12	100	15	Inv-150	0
13	100	15	Inv-150	0
14	200	50	Inv-200	1
15	300	100	Inv-250	0
16	300	100	Inv-250	0
17	400	200	Inv-300	1
18	500	300	Inv-400	0
19	500	300	Inv-400	0
Sheet1

Power Query Solution

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type),
    #"Grouped Rows" = Table.Group(#"Added Index", {"Customer"}, {{"Duplicate", each Table.RowCount(_), Int64.Type}, {"All", each _, type table [Customer=number, Amount=number, Invoice=text, Index=number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each if [Duplicate]>1 then 0 else 1),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Duplicate"}),
    #"Expanded All" = Table.ExpandTableColumn(#"Removed Columns", "All", {"Amount", "Invoice"}, {"All.Amount", "All.Invoice"})
in
    #"Expanded All"

 

[pto160]

Thank you so much. That is a fantastic Power Query solution. I am trying to learn more about using Power Query.

Is there a formula solution I wonder?

 

[Peter_SSs]

Is there a formula solution I wonder?

Would this work for you?
Put a 1 in D1 then the formula shown, copied down.
If you want a different heading, would swapping the 0s and 1s be acceptable as shown in column E?

22 11 08.xlsm
	A	B	C	D	E
1	Customer	Amount	Invoice	1	Hdr
2	100	15	Inv-150	0	1
3	100	15	Inv-150	0	1
4	200	50	Inv-200	1	0
5	300	100	Inv-250	0	1
6	300	100	Inv-250	0	1
7	400	200	Inv-300	1	0
8	500	300	Inv-400	0	1
9	500	300	Inv-400	0	1
0_1

Cell Formulas
Range	Formula
D2:D9	D2	=IF(A2=A1,D1,1-D1)
E2:E9	E2	=IF(A2=A1,E1,1-N(E1))

 

[pto160]

Thanks Peter. Yes swapping the zeros and ones would definitely work for column E.
This will really help me set up conditional formatting where I can group the ones for example with a yellow color.

 

[Peter_SSs]

Sure, that makes for a very simple CF formula. You can even hide the helper column if you want.

22 11 08.xlsm
	A	B	C	D
1	Customer	Amount	Invoice	Group
2	100	15	Inv-150	1
3	100	15	Inv-150	1
4	200	50	Inv-200	0
5	300	100	Inv-250	1
6	300	100	Inv-250	1
7	400	200	Inv-300	0
8	500	300	Inv-400	1
9	500	300	Inv-400	1
0_1

Cell Formulas
Range	Formula
D2:D9	D2	=IF(A2=A1,D1,1-N(D1))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:D9	Expression	=$D2	text	NO

 

[pto160]

Thank you so much for the conditional formatting formula. It works great.

 

[Peter_SSs]

You're welcome. Glad it helped. Thanks for the follow-up.