Returning values from an array or filter

[michaelkellyfrombelfast]

Hello everyone, the first post excel. Excel isn't my strongest skill, but I've been at this all morning and can't work it out. Perhaps you can help with the challenge!

I have a table of data (its 100,000 lines long).

In it, I have some fields: (see below for an example). The data differs in that a product can have 3-30 lines depending on its range. this is for a clothing company.

I'm building this data up from another table, but in essence, I need to add image files based on a product.

So if the GROUPCODE is the same (example ABC123), then the first unique image URL should be posted in the IMAGE1 Column. Secondly, the IMAGE2 column takes another image URL if it exists (belonging to the ABC123 group) and the third column (and so on) listing the URLs.


Products can have up to 12 unique images. This are based on the size and colour combinations.

I'm assuming this is some form of filtering the results in an Array within the formula, and selecting the first, second and third entries and posting them accordingly.

Eg URL for Image has 3 unique entries based on the group code ABC123, so post those entries in the Image columns.

There are likely many ways to do this. But I'm stuck!

Type	GroupCode	Product(Unique)	URLforImage	Image1	Image2	Image3		Size	Colour
Product	ABC123	ABC123	/ABC/OrangeShirt/0001.jpg	/ABC/OrangeShirt/0001.jpg	/ABC/GreenShirt/001.jpg	/ABC/Yellow/001.jpg
SKU	ABC123	ABC1234A	/ABC/OrangeShirt/0001.jpg					Small	Orange
SKU	ABC123	ABC1234B	/ABC/OrangeShirt/0001.jpg					Medium	Orange
SKU	ABC123	ABC1234C	/ABC/OrangeShirt/0001.jpg					Large	Orange
SKU	ABC123	ABC123GS	/ABC/GreenShirt/001.jpg					Small	Green
SKU	ABC123	ABC123GS	/ABC/GreenShirt/001.jpg					Medium	Green
SKU	ABC123	ABC123YS	/ABC/Yellow/001.jpg					Small	Yellow
SKU	ABC123	ABC123YS	/ABC/Yellow/001.jpg					Medium	Yellow
SKU	ABC123	ABC123YS	/ABC/Yellow/001.jpg					Large	Yellow
SKU	ABC123	ABC123YS	/ABC/Yellow/001.jpg					XLarge	Yellow
Product	MIKE123	MIKE123	/MIKE/OrangeShirt/0001.jpg	/MIKE/OrangeShirt/0001.jpg
SKU	MIKE123	MIKE1234A	/MIKE/OrangeShirt/0001.jpg					Small	Orange
SKU	MIKE123	MIKE1234A	/MIKE/OrangeShirt/0001.jpg					Medium	Orange
SKU	MIKE123	MIKE1234A	/MIKE/OrangeShirt/0001.jpg					Large	Orange

 

[steve the fish]

Judged just by your example you could do:

=IF(A2="Product",TRANSPOSE(UNIQUE(FILTER(D:D,(A:A="SKU")*(B:B=B2)))),"")

 

[michaelkellyfrombelfast]

Thanks for this, it really help me understand it. I ended up making some tweaks as I needed to look up the list and transpose a few results (not just one). I wouldn't have gotten there without your help though. I really appreciate it! Thanks very much

=IF(A2="Product",(INDEX(UNIQUE(FILTER(D:D,B:B=B2,"")),SEQUENCE(1,5))),"")

For those that find this.

The first Part is a TRUE or FALSE. So if A2 = "Product", then fire ahead and do something, if not write a blank (the last part of the equation "")

OK if PRODUCT (which it is) then proceed.

So starting from the outside using INDEX

"Return 5 things from inside the brackets (this is the INDEX) and transpose them, or write them horizontally. That's this bit INDEX, and SEQUENCE(1,5)" combination.

The array we get back from the inside of this function sometimes contains 1 element, sometimes 20, so setting the sequence to 5, means we only get 5 of these things. We could change that to 3 and get 3 of them.

UNIQUE, make something inside the array, UNIQUE, show only 1 of its kind. So from the array, we want to find unique values (which we will reduce to a total of 5 eventually).

FILTER what gets passed to Unique, and filter the stuff in the column D (URL for IMAGE). The filter will mean the array will be based on the Groupcode
We filter it based on what in L2 (which is the GROUPCODE for 10 items in this case). So we want a list of URLS but only if they match the GroupCode.
As we copy down the formula, the group code will change.

So this takes the list of the GROUPCODE, lists a bunch of URLs (in the case above that would be 10 items, of which the UNIQUE part reduces to 3, as three of the URLs are unique)

So only things with the GROUP code are looked at, only UNIQUE items from that are returned (a lot of them, but in this case 3, there could be more, but we stop at 5 so the returns list doesn't get too big).

Index reduces this down to 5 things in that array, and transposes them.

Hopefully I explained that enough!