+ Reply to Thread
Results 1 to 10 of 10

Formula for Formatting Data

  1. #1
    Registered User
    Join Date
    04-07-2017
    Location
    Camp Hill, PA
    MS-Off Ver
    2016
    Posts
    8

    Formula for Formatting Data

    Hi everyone,

    I have a spreadsheet with a lot of information.

    the two columns in question are item number and color.

    first 6 rows; r444 item number, different color for all 6

    next 13 rows; t555 item number, different color for all 13

    next 7 rows: y222 item number, different color for all 6

    I would like to have a make a formula where the color corresponding to the first item number in the set of same item numbers is the same for the balance of the set.

    i.e. Present: r444:blue, r444;yellow, r444;black
    Would like to get it: r444;blue, r444;blue, r444;blue

    There are other columns not effecting this action that are unique to each row.

    Thank You for all of your help!

    Robert Haas

  2. #2
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    27,999

    Re: Formula for Formatting Data

    Attach a sample workbook (not image).

    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
    04-07-2017
    Location
    Camp Hill, PA
    MS-Off Ver
    2016
    Posts
    8

    Re: Formula for Formatting Data

    Thanks John! Spreadsheet is attached showing sample existing and desired formatting.
    Attached Files Attached Files

  4. #4
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    27,999

    Re: Formula for Formatting Data

    Try

    in C2

    =IF($A2<>$A1,$B2,$B1)

    OR

    in B2


    ="www.sample.com/TFF-" &VLOOKUP(A2,$F$2:$G$4,2,0)

    where F2:G4 is a "lookup" table.

    See attached
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    04-07-2017
    Location
    Camp Hill, PA
    MS-Off Ver
    2016
    Posts
    8

    Re: Formula for Formatting Data

    Thanks John!

    I'll give it a try and let you know how it works out.

  6. #6
    Registered User
    Join Date
    04-07-2017
    Location
    Camp Hill, PA
    MS-Off Ver
    2016
    Posts
    8

    Re: Formula for Formatting Data

    Tried the formula and looked at the look up table. For the formula, not sure it will work, because the number of cells in the column between items is always different, and they're referencing a different starting point. I do think the look up table will work, and will play around with that some more. I'm going to have to find a way to get all of the unique values in the Handle column, and they're starting reference point in the Image Src column.

    I really appreciate your help John, thank you.

  7. #7
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    27,999

    Re: Formula for Formatting Data

    Perhaps post another sample file which is more representative of your data.

  8. #8
    Registered User
    Join Date
    04-07-2017
    Location
    Camp Hill, PA
    MS-Off Ver
    2016
    Posts
    8

    Re: Formula for Formatting Data

    Sure thing, I've desensitized the data and hidden columns not in question, please feel free to expand if needed. Columns in question are A, W and AO. It is a shopify product upload spreadsheet. A is the item number or handle, W is the static image that will appear for the product when it is first opened, and AO is the variant image that can be changed when browsing the product. Because each set all references the same product, the static image must remain the same. I got here by taking their item numbers and including the path. Their item numbers are actually like 545-31 for black 545-32 for red, 545-41 for grey, etc. That works great for the variant images, but not for the static images. There is no file that just has "545" when referencing the example from the last sentence. So basically, my formula to get the paths was to take column a, and a column with the path, call it ZZ, and say =a1&zz1; =a2&zz2, and then expand through the entire column on the spreadsheet.
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    04-07-2017
    Location
    Camp Hill, PA
    MS-Off Ver
    2016
    Posts
    8

    Re: Formula for Formatting Data

    Sorry, just fixed sample formatting on the sample spreadsheet. All versions of sizes get the same image. I've fixed the data to show that.
    Attached Files Attached Files

  10. #10
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    27,999

    Re: Formula for Formatting Data

    Not sure I folllow ( you mention "path" but what is it?)

    Column W appears to be

    ="www.sample.com/" &A2&"1"

+ 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. [SOLVED] Issue with an & formula and data formatting
    By chrisellis250 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 08-23-2017, 05:33 AM
  2. Formatting Data Within A Formula
    By TheDudeness in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 04-22-2015, 12:27 AM
  3. [SOLVED] Conditional border formatting formula (after repeat 5 data)
    By Jhon Mustofa in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-09-2014, 12:02 PM
  4. copy formula and the conditional formatting to the last row of data
    By shaz0503 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-11-2014, 01:09 AM
  5. [SOLVED] Formatting a table to pull data - Array formula?
    By Student1990 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 10-09-2013, 08:09 AM
  6. Conditional formatting formula -Data range?
    By elfiky in forum Excel General
    Replies: 2
    Last Post: 09-27-2011, 02:44 AM
  7. Conditional Formatting Formula for zig zagging data
    By ChemistB in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 05-03-2007, 11:01 AM

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