Formula needed to reorganize data

illoguy

New Member
Joined
Jul 25, 2019
Messages
5
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:
testing-formulas-v1.JPG

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]


 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
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))),"")
 
Upvote 0
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
 
Upvote 0
It's a normal formula so doesn't require CSE.

This is what I get with your sample data


Book1
ABCDEF
220503397fa01f48-62af-4580-aa4c-9818ab6620503397-fa01f48-62af-4580-aa4c-9818ab66.jpg2050339720509288
32050339743c80889-049-4ce0-ac80-fc6eeff1cc820503397-43c80889-049-4ce0-ac80-fc6eeff1cc8.jpg20503397-fa01f48-62af-4580-aa4c-9818ab66.jpg20509288-8f306b60-2c2b-412f-80ec-e49512e519.jpg
420503397999748e-24fe-414-b8-763b0f89aa6320503397-999748e-24fe-414-b8-763b0f89aa63.jpg20503397-43c80889-049-4ce0-ac80-fc6eeff1cc8.jpg20509288-ee1f76-bf33-409-a3c-301231514bfb.jpg
5205033976aef6383-6e9c-4c4-a4c6-68ccb417ecb20503397-6aef6383-6e9c-4c4-a4c6-68ccb417ecb.jpg20503397-999748e-24fe-414-b8-763b0f89aa63.jpg20509288-361c8b29-4a49-48f1-8336-18b63863f5.jpg
620503397a99bac40-cee9-4feb-884e-5772162be74120503397-a99bac40-cee9-4feb-884e-5772162be741.jpg20503397-6aef6383-6e9c-4c4-a4c6-68ccb417ecb.jpg20509288-917016bc-8bc-4ac-b7e6-e27c639ec.jpg
7205033977670966-ff3c-465-8655-b7a8ae0a3920503397-7670966-ff3c-465-8655-b7a8ae0a39.jpg20503397-a99bac40-cee9-4feb-884e-5772162be741.jpg20509288-cb79b9a-51fb-4c4c-855-2647586b41a5.jpg
820503397f7ac32e0-9c8-42fe-9963-a6092e366e920503397-f7ac32e0-9c8-42fe-9963-a6092e366e9.jpg20503397-7670966-ff3c-465-8655-b7a8ae0a39.jpg
9205033979b1a59a7-50e6-4e31-ab8f-69f891bf69e20503397-9b1a59a7-50e6-4e31-ab8f-69f891bf69e.jpg20503397-f7ac32e0-9c8-42fe-9963-a6092e366e9.jpg
10205033971037b5f8-b46f-4526-a42b-5957c7a5665720503397-1037b5f8-b46f-4526-a42b-5957c7a56657.jpg20503397-9b1a59a7-50e6-4e31-ab8f-69f891bf69e.jpg
112050339708baefaa-7b9e-479c-b74b-5e7329c417720503397-08baefaa-7b9e-479c-b74b-5e7329c4177.jpg20503397-1037b5f8-b46f-4526-a42b-5957c7a56657.jpg
1220503397281c149-688a-41ff-abe1-bfe6a962954320503397-281c149-688a-41ff-abe1-bfe6a9629543.jpg20503397-08baefaa-7b9e-479c-b74b-5e7329c4177.jpg
1320503397ee44ba5-ef00-492-a90e-f69cf961ae520503397-ee44ba5-ef00-492-a90e-f69cf961ae5.jpg20503397-281c149-688a-41ff-abe1-bfe6a9629543.jpg
1420503397cf5b883-b345-4594-84f6-b7324a53b34020503397-cf5b883-b345-4594-84f6-b7324a53b340.jpg20503397-ee44ba5-ef00-492-a90e-f69cf961ae5.jpg
15205092888f306b60-2c2b-412f-80ec-e49512e51920509288-8f306b60-2c2b-412f-80ec-e49512e519.jpg20503397-cf5b883-b345-4594-84f6-b7324a53b340.jpg
1620509288ee1f76-bf33-409-a3c-301231514bfb20509288-ee1f76-bf33-409-a3c-301231514bfb.jpg
1720509288361c8b29-4a49-48f1-8336-18b63863f520509288-361c8b29-4a49-48f1-8336-18b63863f5.jpg
1820509288917016bc-8bc-4ac-b7e6-e27c639ec20509288-917016bc-8bc-4ac-b7e6-e27c639ec.jpg
1920509288cb79b9a-51fb-4c4c-855-2647586b41a520509288-cb79b9a-51fb-4c4c-855-2647586b41a5.jpg
Sheet1
Cell Formulas
RangeFormula
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.
 
Last edited:
Upvote 0
Thinking about it. Do you have the SKU numbers in E2, F2 etc?
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top