+ Reply to Thread
Results 1 to 7 of 7

Transpose data (shopify product list related)

  1. #1
    Registered User
    Join Date
    05-06-2019
    Location
    California
    MS-Off Ver
    2013
    Posts
    3

    Transpose data (shopify product list related)

    Hello! I'd consider myself a beginner to excel though I do understand how to write formulas and use most of the tools on excel.

    I have a product list i am trying to organize which was exported from shopify. The problem is the images (primary, secondary, etc) populate the cells via a column with products varying from 1-5 images. example..


    product 1 | image 1
    -------------| image 2
    -------------| image 3
    product 2 | image 1
    -------------| image 2


    I need to organize the product images horizontally as I will be using the columns to populate a product list to upload to another platform.

    product 1 | image 1 | image 2 | image 3
    product 2 | image 1 | image 2


    I am looking for a way to do this efficiently as i have about 18000 rows to go through... which would take way too long to simply copy / transpose.

    Please let me know if this isn't clear.

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 365
    Posts
    20,449

    Re: Transpose data (shopify product list related)

    Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

  3. #3
    Registered User
    Join Date
    05-06-2019
    Location
    California
    MS-Off Ver
    2013
    Posts
    3

    Re: Transpose data (shopify product list related)

    Added Sample Attachment
    Attached Files Attached Files

  4. #4
    Forum Expert
    Join Date
    10-15-2018
    Location
    MA, USA
    MS-Off Ver
    2010, 2019
    Posts
    1,478

    Re: Transpose data (shopify product list related)

    1. Place the following formula in F2 then copy across as far as you need to cover at least the max number of images you expect for any product, then copy down to cover all 18,000 rows.
    2. Formula: copy to clipboard
      Please Login or Register  to view this content.
    3. Copy cols F:K (or however many columns you decided in the previous step) then paste-values in order to replace the formulas with fixed values.
    4. Sort the worksheet by "Title"
    5. Select and delete all rows (which will now be contiguous) where "Title" is blank.

    That's it!

    Note - if this is a one-off exercise then this approach should work for you. If however this is something that you will need to do on a regular basis then a VBA solution may be better.

  5. #5
    Registered User
    Join Date
    05-06-2019
    Location
    California
    MS-Off Ver
    2013
    Posts
    3

    Re: Transpose data (shopify product list related)

    Quote Originally Posted by GeoffW283 View Post
    1. Place the following formula in F2 then copy across as far as you need to cover at least the max number of images you expect for any product, then copy down to cover all 18,000 rows.
    2. Formula: copy to clipboard
      Please Login or Register  to view this content.
    3. Copy cols F:K (or however many columns you decided in the previous step) then paste-values in order to replace the formulas with fixed values.
    4. Sort the worksheet by "Title"
    5. Select and delete all rows (which will now be contiguous) where "Title" is blank.

    That's it!

    Note - if this is a one-off exercise then this approach should work for you. If however this is something that you will need to do on a regular basis then a VBA solution may be better.
    Spot on! Ill be studying these functions thoroughly for further use and manipulation. Thank you!

  6. #6
    Forum Expert
    Join Date
    10-15-2018
    Location
    MA, USA
    MS-Off Ver
    2010, 2019
    Posts
    1,478

    Re: Transpose data (shopify product list related)

    Glad to help. Thanks for the feedback and reputation points

  7. #7
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 365
    Posts
    20,449

    Re: Transpose data (shopify product list related)

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Transpose a list of related values
    By Surajgk in forum Excel General
    Replies: 3
    Last Post: 03-13-2017, 12:52 AM
  2. Changing inventory in shopify using formulas!
    By seanfrank1994 in forum Excel General
    Replies: 1
    Last Post: 02-11-2016, 04:09 PM
  3. Replies: 8
    Last Post: 02-03-2015, 03:42 PM
  4. retrieve data related to selection in dro down list.
    By RAexcel in forum Excel General
    Replies: 2
    Last Post: 10-24-2014, 08:58 AM
  5. [SOLVED] transpose related data from row into columns
    By Lisa4legin in forum Excel General
    Replies: 6
    Last Post: 11-10-2013, 04:50 PM
  6. Replies: 0
    Last Post: 10-01-2012, 02:57 PM
  7. HELP on TRANSPOSE related function.
    By sunj99 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-01-2005, 11:57 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1