Friday, October 4, 2013

Question:

( by Ben )

Ok, I have a major head scratcher! My data is currently like the table below and I need it to be sorted by the Product Grouping ID. I have hundreds of rows so need a way to do this programmatically. Not even sure if this is too difficult for a spreadsheet.

Would be very grateful for any help or pointers.

Thanks

CURRENT DATA ARRANGEMENT

 CURRENT DATA ARRANGEMENT SKU Product ID Title Product Grouping ID FS01 6297 Nike Bears Home Shirt 2012 2014 6297 FS02 6298 Nike Bears Home Shirt 2012 2014 6297 FS03 6299 Nike Bears Home Shirt 2012 2014 6297 FS04 6300 Nike Bears Away Shirt 2013 2014 6298 FS05 6301 Nike Bears Away Shirt 2013 2014 6298 FS06 6302 Nike Bears Away Shirt 2013 2014 6298 FS07 6303 Nike Bears Away Shirt 2013 2014 6298 FS08 6304 Nike Bears Away Shirt 2013 2014 6298 FS09 6305 Nike Bears Away Shirt 2013 2014 6298 FS10 6306 Nike Bears Away Shirt 2013 2014 Junior 6299 FS11 6307 Nike Bears Away Shirt 2013 2014 Junior 6299 FS12 6308 Nike Bears Away Shirt 2013 2014 Junior 6299

REFERRED DATA ARRANGEMENT

 Product Grouping ID Title SKU 6297 Nike Bears Home Shirt 2012 2014 FS01,FS02,FS03 6298 Nike Bears Away Shirt 2013 2014 FS04,FS05,FS06,FS07,FS08,FS09 6299 Nike Bears Away Shirt 2013 2014 Junior FS10,FS11,FS12

Solution:

Have a look at the following screenshot of Sheet "Sheet1":

Have a look at the following screenshot of Sheet "Sheet2":

In the above sheet I have the following formula in Cell A1:
=unique(Query(Sheet1!C2:D;"select D,C";1))

and the following formula in Cell C2:
=join(",";transpose(filter(Sheet1!A\$2:A;Sheet1!C\$2:C=B2;Sheet1!D\$2:D=A2)))

and then you can drag the above formula to the cells below as far as needed, and by doing so you will get the following formula in Cell C3:
=join(",";transpose(filter(Sheet1!A\$2:A;Sheet1!C\$2:C=B3;Sheet1!D\$2:D=A3)))

I hope the above solution will help you, and if you need more help then please do comment below on this blog itself, I will try to help you out.

I also take up private and confidential projects:
If this blog post was helpful to you, and if you think you want to help me too and make my this blog survive then please donate here: http://igoogledrive.blogspot.com/2012/09/donate.html

Thanks,

1 comment:

1. Hi Kishan,

Thanks a lot man. I found your blog is very useful and it helps me so much.

Chairul, Indonesia