Formula needed to reorganize data

[illoguy]

Hello,

I'm a novice Excel user, but know basic formulas, but this need is stumping me.

Need to find a formula to automate 100's of product image names based on the product SKU. I have a SKU number, imagename.jpg in 2 columns and need to have that list of individual SKUs in a new column with the corresponding images associated with that SKU. Each SKU has a different number of images. The image names have the SKU number in the name. I can Transpose the different SKUs in separate columns but I don't know how to set a formula to go to the next column once there is no more images with that SKU in the name.

I was thinking this could be done with VLOOKUP, INDEX and MATCH and/or IF formulas as combined functions.


IMAGE of what I'm trying to do:

https://www.dropbox.com/s/43lzr9xlpz5w3cd/testing-formulas-v1.JPG?dl=0

Actual Excel Document:
[FONT=wf_segoe-ui_normal]https://www.dropbox.com/s/67jl8c4vzeeh5xx/testing-formulas-v1.xlsx?dl=0


Your expertise would be greatly appreciated!
Jay
[/FONT]

 

[Fluff]

Hi & welcome to MrExcel.
In E3 copied down & across
=IFERROR(INDEX($C$2:$C$30,AGGREGATE(15,6,(ROW($C$2:$C$30)-ROW($C$2)+1)/(--LEFT($C$2:$C$30,8)=E$2),ROWS($C$1:$C1))),"")

 

[illoguy]

Hi & welcome to MrExcel.
In E3 copied down & across
=IFERROR(INDEX($C$2:$C$30,AGGREGATE(15,6,(ROW($C$2:$C$30)-ROW($C$2)+1)/(--LEFT($C$2:$C$30,8)=E$2),ROWS($C$1:$C1))),"")

Thanks you for your quick reply Fluff.

I tried your lengthy formula in E3 and the result was blank. Do I need to do a special return like Shift+Option+Return?

I tried copying down and across with nothing showing.

I really hope this can work.

Thank you for your help!
Jay

 

[Fluff]

It's a normal formula so doesn't require CSE.

This is what I get with your sample data

Book1
	A	B	C	D	E	F
2	20503397	fa01f48-62af-4580-aa4c-9818ab66	20503397-fa01f48-62af-4580-aa4c-9818ab66.jpg		20503397	20509288
3	20503397	43c80889-049-4ce0-ac80-fc6eeff1cc8	20503397-43c80889-049-4ce0-ac80-fc6eeff1cc8.jpg		20503397-fa01f48-62af-4580-aa4c-9818ab66.jpg	20509288-8f306b60-2c2b-412f-80ec-e49512e519.jpg
4	20503397	999748e-24fe-414-b8-763b0f89aa63	20503397-999748e-24fe-414-b8-763b0f89aa63.jpg		20503397-43c80889-049-4ce0-ac80-fc6eeff1cc8.jpg	20509288-ee1f76-bf33-409-a3c-301231514bfb.jpg
5	20503397	6aef6383-6e9c-4c4-a4c6-68ccb417ecb	20503397-6aef6383-6e9c-4c4-a4c6-68ccb417ecb.jpg		20503397-999748e-24fe-414-b8-763b0f89aa63.jpg	20509288-361c8b29-4a49-48f1-8336-18b63863f5.jpg
6	20503397	a99bac40-cee9-4feb-884e-5772162be741	20503397-a99bac40-cee9-4feb-884e-5772162be741.jpg		20503397-6aef6383-6e9c-4c4-a4c6-68ccb417ecb.jpg	20509288-917016bc-8bc-4ac-b7e6-e27c639ec.jpg
7	20503397	7670966-ff3c-465-8655-b7a8ae0a39	20503397-7670966-ff3c-465-8655-b7a8ae0a39.jpg		20503397-a99bac40-cee9-4feb-884e-5772162be741.jpg	20509288-cb79b9a-51fb-4c4c-855-2647586b41a5.jpg
8	20503397	f7ac32e0-9c8-42fe-9963-a6092e366e9	20503397-f7ac32e0-9c8-42fe-9963-a6092e366e9.jpg		20503397-7670966-ff3c-465-8655-b7a8ae0a39.jpg
9	20503397	9b1a59a7-50e6-4e31-ab8f-69f891bf69e	20503397-9b1a59a7-50e6-4e31-ab8f-69f891bf69e.jpg		20503397-f7ac32e0-9c8-42fe-9963-a6092e366e9.jpg
10	20503397	1037b5f8-b46f-4526-a42b-5957c7a56657	20503397-1037b5f8-b46f-4526-a42b-5957c7a56657.jpg		20503397-9b1a59a7-50e6-4e31-ab8f-69f891bf69e.jpg
11	20503397	08baefaa-7b9e-479c-b74b-5e7329c4177	20503397-08baefaa-7b9e-479c-b74b-5e7329c4177.jpg		20503397-1037b5f8-b46f-4526-a42b-5957c7a56657.jpg
12	20503397	281c149-688a-41ff-abe1-bfe6a9629543	20503397-281c149-688a-41ff-abe1-bfe6a9629543.jpg		20503397-08baefaa-7b9e-479c-b74b-5e7329c4177.jpg
13	20503397	ee44ba5-ef00-492-a90e-f69cf961ae5	20503397-ee44ba5-ef00-492-a90e-f69cf961ae5.jpg		20503397-281c149-688a-41ff-abe1-bfe6a9629543.jpg
14	20503397	cf5b883-b345-4594-84f6-b7324a53b340	20503397-cf5b883-b345-4594-84f6-b7324a53b340.jpg		20503397-ee44ba5-ef00-492-a90e-f69cf961ae5.jpg
15	20509288	8f306b60-2c2b-412f-80ec-e49512e519	20509288-8f306b60-2c2b-412f-80ec-e49512e519.jpg		20503397-cf5b883-b345-4594-84f6-b7324a53b340.jpg
16	20509288	ee1f76-bf33-409-a3c-301231514bfb	20509288-ee1f76-bf33-409-a3c-301231514bfb.jpg
17	20509288	361c8b29-4a49-48f1-8336-18b63863f5	20509288-361c8b29-4a49-48f1-8336-18b63863f5.jpg
18	20509288	917016bc-8bc-4ac-b7e6-e27c639ec	20509288-917016bc-8bc-4ac-b7e6-e27c639ec.jpg
19	20509288	cb79b9a-51fb-4c4c-855-2647586b41a5	20509288-cb79b9a-51fb-4c4c-855-2647586b41a5.jpg
Sheet1

Cell Formulas
Range	Formula
E3	=IFERROR(INDEX($C$2:$C$30,AGGREGATE(15,6,(ROW($C$2:$C$30)-ROW($C$2)+1)/(--LEFT($C$2:$C$30,8)=E$2),ROWS($C$1:$C1))),"")
F3	=IFERROR(INDEX($C$2:$C$30,AGGREGATE(15,6,(ROW($C$2:$C$30)-ROW($C$2)+1)/(--LEFT($C$2:$C$30,8)=F$2),ROWS($C$1:$C1))),"")

Only shown the first two, otherwise it's too wide to fit the post.

 

[Fluff]

Thinking about it. Do you have the SKU numbers in E2, F2 etc?

 

[illoguy]

Thinking about it. Do you have the SKU numbers in E2, F2 etc?

That's what I would like. I noticed that the row 1 was deleted on your images. I had some titles. Does that matter?

Mine is still blank with a simple return.
Your images that you posted gives me hope that it will work. Wow
Thanks! Jay

 

[illoguy]

I'm on Mac Desktop latest version. Not sure if that makes a difference.
Jay

 

[Fluff]

In E2 & fill right
=IFERROR(INDEX($A$2:$A$30,MATCH(0,INDEX(COUNTIF($D2:D2,$A$2:$A$30),0),0)),"")
This will give you the SKU numbers for the other formula to use.

 

[illoguy]

OK, I got it working!

For more columns I just change the Column letter:
E$2),ROWS($C$1:$C1))),"")
F$2),ROWS($C$1:$C1))),"")
G$2),ROWS($C$1:$C1))),"")
.etc.

I'll let you know if there is anything else! Much appreciated!

THANK YOU!
Jay

 

[Fluff]

You're welcome & thanks for the feedback.

You don't need to change the column letters.
You can simply drag the formula to the right & everything will sort itself out.