New Columns - Group By

[micky007]

Hi,



I'm in need of some help please. I'm trying to do a stock report for different depots so i can see which depot has what available in stock. At the moment i have a CSV file with all the data like the below:

DepotID	Available	Product
1234	1	Coca-Cola 330ml
1234	0	Dr Pepper 500ml
1234	0	Fanta 330ml
1234	1	Monster Energy 500ml
1234	1	Sprite 500ml

What I'd like is to have just the 1 row (for each DepotID) and then a column for each product with the value 0 or 1.



0 means not available

1 means it is available



How do i go about doing this?



Thank you for your help in advance!

 

[etaf]

a pivot table should help with that

See below , do you know pivot tables ?
Also add the version of Excel you have , as that version will also make a difference to what functions are available
pivot tables are in old versions of excel anyway - But some new functions are NOT

Book4
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	DepotID	Available	Product
2	1234	1	Coca-Cola 330ml			Sum of Available	Column Labels
3	1234	0	Dr Pepper 500ml			Row Labels	Coca-Cola 330ml	Dr Pepper 500ml	Fanta 330ml	Monster Energy 500ml	Sprite 500ml	Grand Total
4	1234	0	Fanta 330ml			1234	1	0	0	1	1	3
5	1234	1	Monster Energy 500ml			Grand Total	1	0	0	1	1	3
6	1234	1	Sprite 500ml
Sheet1

 

[micky007]

Hi,

Thanks for your response, I'm wanting to do this in Power BI as the excel file has too many rows to open the full file. Otherwise I'd of done as you mentioned

 

[etaf]

sorry, i didnt notice the forum , and i'm on a Mac and excel does not have Power Bi yet