Formula needed to reorganize data

I

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

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
Sort by date Sort by votes
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
Fluff said:
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))),"")
Click to expand...

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
Fluff said:
Thinking about it. Do you have the SKU numbers in E2, F2 etc?
Click to expand...

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
You must log in or register to reply here.

Similar threads

H
  • Question Question
Lookup (or Search) for a value in one column across a range of columns, then return the corresponding value for a different column
Replies
6
Views
776
hagia_sofia
hagia_sofia
T
  • Question Question
Lookup within a lookup
2
Replies
10
Views
777
TimeForTim
T
B
  • Question Question
Transpose column while matching to a lookup value
Replies
2
Views
300
alansidman
alansidman
Q
  • Question Question
Macro needed to insert 4 rows in table, copy data from above, copy data from another sheet and paste special (transpose values) to those new rows...re
Replies
1
Views
402
sijpie
sijpie
A
  • Question Question
how to Image should come by entering SKU no in Excel (images are copy-paste in base file for lookup,index-match or VBA option)
Replies
1
Views
662
OaklandJim
O

Forum statistics

Threads
1,224,823
Messages
6,181,176
Members
453,021
Latest member
Justyna P

Share this page

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