# Formula needed to reorganize data

#### illoguy

##### New Member
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]

### 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
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
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))),"")

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.

Jay

#### Fluff

##### MrExcel MVP, Moderator
It's a normal formula so doesn't require CSE.

This is what I get with your sample data

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

Last edited:

#### Fluff

##### MrExcel MVP, Moderator

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

#### illoguy

##### New Member
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

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

#### Fluff

##### MrExcel MVP, Moderator
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
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
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.

Replies
2
Views
79
Replies
2
Views
69
Replies
0
Views
81
Replies
9
Views
131
Replies
4
Views
207

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.

### Which adblocker are you using?

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

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