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

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
59,878
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Jul 25, 2019
Messages
5
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
59,878
Office Version
  1. 365
Platform
  1. Windows
It's a normal formula so doesn't require CSE.

This is what I get with your sample data

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="text-align: right;background-color: #FFFF00;;">20503397</td><td style=";">fa01f48-62af-4580-aa4c-9818ab66</td><td style=";">20503397-fa01f48-62af-4580-aa4c-9818ab66.jpg</td><td style="text-align: right;;"></td><td style="background-color: #FFFF00;;">20503397</td><td style="background-color: #FFC000;;">20509288</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;background-color: #FFFF00;;">20503397</td><td style=";">43c80889-049-4ce0-ac80-fc6eeff1cc8</td><td style=";">20503397-43c80889-049-4ce0-ac80-fc6eeff1cc8.jpg</td><td style="text-align: right;;"></td><td style=";">20503397-fa01f48-62af-4580-aa4c-9818ab66.jpg</td><td style=";">20509288-8f306b60-2c2b-412f-80ec-e49512e519.jpg</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: right;background-color: #FFFF00;;">20503397</td><td style=";">999748e-24fe-414-b8-763b0f89aa63</td><td style=";">20503397-999748e-24fe-414-b8-763b0f89aa63.jpg</td><td style="text-align: right;;"></td><td style=";">20503397-43c80889-049-4ce0-ac80-fc6eeff1cc8.jpg</td><td style=";">20509288-ee1f76-bf33-409-a3c-301231514bfb.jpg</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-align: right;background-color: #FFFF00;;">20503397</td><td style=";">6aef6383-6e9c-4c4-a4c6-68ccb417ecb</td><td style=";">20503397-6aef6383-6e9c-4c4-a4c6-68ccb417ecb.jpg</td><td style="text-align: right;;"></td><td style=";">20503397-999748e-24fe-414-b8-763b0f89aa63.jpg</td><td style=";">20509288-361c8b29-4a49-48f1-8336-18b63863f5.jpg</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="text-align: right;background-color: #FFFF00;;">20503397</td><td style=";">a99bac40-cee9-4feb-884e-5772162be741</td><td style=";">20503397-a99bac40-cee9-4feb-884e-5772162be741.jpg</td><td style="text-align: right;;"></td><td style=";">20503397-6aef6383-6e9c-4c4-a4c6-68ccb417ecb.jpg</td><td style=";">20509288-917016bc-8bc-4ac-b7e6-e27c639ec.jpg</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="text-align: right;background-color: #FFFF00;;">20503397</td><td style=";">7670966-ff3c-465-8655-b7a8ae0a39</td><td style=";">20503397-7670966-ff3c-465-8655-b7a8ae0a39.jpg</td><td style="text-align: right;;"></td><td style=";">20503397-a99bac40-cee9-4feb-884e-5772162be741.jpg</td><td style=";">20509288-cb79b9a-51fb-4c4c-855-2647586b41a5.jpg</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style="text-align: right;background-color: #FFFF00;;">20503397</td><td style=";">f7ac32e0-9c8-42fe-9963-a6092e366e9</td><td style=";">20503397-f7ac32e0-9c8-42fe-9963-a6092e366e9.jpg</td><td style="text-align: right;;"></td><td style=";">20503397-7670966-ff3c-465-8655-b7a8ae0a39.jpg</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style="text-align: right;background-color: #FFFF00;;">20503397</td><td style=";">9b1a59a7-50e6-4e31-ab8f-69f891bf69e</td><td style=";">20503397-9b1a59a7-50e6-4e31-ab8f-69f891bf69e.jpg</td><td style="text-align: right;;"></td><td style=";">20503397-f7ac32e0-9c8-42fe-9963-a6092e366e9.jpg</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style="text-align: right;background-color: #FFFF00;;">20503397</td><td style=";">1037b5f8-b46f-4526-a42b-5957c7a56657</td><td style=";">20503397-1037b5f8-b46f-4526-a42b-5957c7a56657.jpg</td><td style="text-align: right;;"></td><td style=";">20503397-9b1a59a7-50e6-4e31-ab8f-69f891bf69e.jpg</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style="text-align: right;background-color: #FFFF00;;">20503397</td><td style=";">08baefaa-7b9e-479c-b74b-5e7329c4177</td><td style=";">20503397-08baefaa-7b9e-479c-b74b-5e7329c4177.jpg</td><td style="text-align: right;;"></td><td style=";">20503397-1037b5f8-b46f-4526-a42b-5957c7a56657.jpg</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style="text-align: right;background-color: #FFFF00;;">20503397</td><td style=";">281c149-688a-41ff-abe1-bfe6a9629543</td><td style=";">20503397-281c149-688a-41ff-abe1-bfe6a9629543.jpg</td><td style="text-align: right;;"></td><td style=";">20503397-08baefaa-7b9e-479c-b74b-5e7329c4177.jpg</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">13</td><td style="text-align: right;background-color: #FFFF00;;">20503397</td><td style=";">ee44ba5-ef00-492-a90e-f69cf961ae5</td><td style=";">20503397-ee44ba5-ef00-492-a90e-f69cf961ae5.jpg</td><td style="text-align: right;;"></td><td style=";">20503397-281c149-688a-41ff-abe1-bfe6a9629543.jpg</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">14</td><td style="text-align: right;background-color: #FFFF00;;">20503397</td><td style=";">cf5b883-b345-4594-84f6-b7324a53b340</td><td style=";">20503397-cf5b883-b345-4594-84f6-b7324a53b340.jpg</td><td style="text-align: right;;"></td><td style=";">20503397-ee44ba5-ef00-492-a90e-f69cf961ae5.jpg</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">15</td><td style="text-align: right;background-color: #FFC000;;">20509288</td><td style=";">8f306b60-2c2b-412f-80ec-e49512e519</td><td style=";">20509288-8f306b60-2c2b-412f-80ec-e49512e519.jpg</td><td style="text-align: right;;"></td><td style=";">20503397-cf5b883-b345-4594-84f6-b7324a53b340.jpg</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">16</td><td style="text-align: right;background-color: #FFC000;;">20509288</td><td style=";">ee1f76-bf33-409-a3c-301231514bfb</td><td style=";">20509288-ee1f76-bf33-409-a3c-301231514bfb.jpg</td><td style="text-align: right;;"></td><td style=";"></td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">17</td><td style="text-align: right;background-color: #FFC000;;">20509288</td><td style=";">361c8b29-4a49-48f1-8336-18b63863f5</td><td style=";">20509288-361c8b29-4a49-48f1-8336-18b63863f5.jpg</td><td style="text-align: right;;"></td><td style=";"></td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">18</td><td style="text-align: right;background-color: #FFC000;;">20509288</td><td style=";">917016bc-8bc-4ac-b7e6-e27c639ec</td><td style=";">20509288-917016bc-8bc-4ac-b7e6-e27c639ec.jpg</td><td style="text-align: right;;"></td><td style=";"></td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">19</td><td style="text-align: right;background-color: #FFC000;;">20509288</td><td style=";">cb79b9a-51fb-4c4c-855-2647586b41a5</td><td style=";">20509288-cb79b9a-51fb-4c4c-855-2647586b41a5.jpg</td><td style="text-align: right;;"></td><td style=";"></td><td style=";"></td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">E3</th><td style="text-align:left">=IFERROR(<font color="Blue">INDEX(<font color="Red">$C$2:$C$30,AGGREGATE(<font color="Green">15,6,(<font color="Purple">ROW(<font color="Teal">$C$2:$C$30</font>)-ROW(<font color="Teal">$C$2</font>)+1</font>)/(<font color="Purple">--LEFT(<font color="Teal">$C$2:$C$30,8</font>)=E$2</font>),ROWS(<font color="Purple">$C$1:$C1</font>)</font>)</font>),""</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">F3</th><td style="text-align:left">=IFERROR(<font color="Blue">INDEX(<font color="Red">$C$2:$C$30,AGGREGATE(<font color="Green">15,6,(<font color="Purple">ROW(<font color="Teal">$C$2:$C$30</font>)-ROW(<font color="Teal">$C$2</font>)+1</font>)/(<font color="Purple">--LEFT(<font color="Teal">$C$2:$C$30,8</font>)=F$2</font>),ROWS(<font color="Purple">$C$1:$C1</font>)</font>)</font>),""</font>)</td></tr></tbody></table></td></tr></table><br />

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

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
59,878
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

illoguy

New Member
Joined
Jul 25, 2019
Messages
5
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

New Member
Joined
Jul 25, 2019
Messages
5

ADVERTISEMENT

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

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
59,878
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Jul 25, 2019
Messages
5
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
59,878
Office Version
  1. 365
Platform
  1. Windows
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.
 

Forum statistics

Threads
1,136,260
Messages
5,674,682
Members
419,520
Latest member
Jennifer4Dillon

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
Top