Question

  • Creator
    Topic
  • #2150496

    Excel Help Needed -Add multiple IF statements to a combined string of cells

    Locked

    by little_dobby ·

    I cannot figure out the best way to make this formula as simple as possible so that I don’t confuse myself!  I have an ecommerce database I’m trying to build in Excel for our website.  I have never made a database-driven website before.  I’m using Excel 2007.  I have no idea how to make a macro, so I’m using a formula.  I need to have the text from multiple cells combined together into one cell (I’m using the A3&”-“&A4 type of formula), because I want to have one cell that I can reference from the database to input the product information for a given product.

    The columns have data that I want turned into bullets after the main product description.  But if there isn’t anything listed in the cell (cell ISBLANK), then I don’t want anything to show up for that cell, and I want it to go onto showing the info. for the next cell and so on. 

    Here are the Columns I’m using for this formula:

    AP = Item Description
    AQ = Item Info. Bullet #1
    AR = Item Info. Bullet #2
    AS = Item Info. Bullet #3
    AT = Item Info. Bullet #4
    AU = Item Info. Bullet #5
    AW = Warranty (i.e. 1 year or 6 months, etc.)
    AX = Warranty Language (i.e. This is a limited warranty…)
    AZ = UL or ETL Listed
    BA = Wattage

    For some items, collumn AW for Warranty will say “1 year,” while for other items this cell is simply blank.  Same thing for whether or not the wattage is listed for that product or if it says it is UL listed or just doesn’t say.

    I can’t figure out how to paste my Excel formula without the site taking the html and converting it to html instead of text only.  So it makes it hard to follow my Excel formula.  If you know how I can even post my formula here in text instead of html, that would be helpful, too!  Thank you!

    Here is my formula so far (the simplified version):

    =CLEAN($AP2&”<ul><b><font face=Arial size=2> <li>”&IF(ISBLANK($AQ2),””,$AQ2&”</li><li>”&$AR2&”</li><li>”&$AS2&”</li><li>”&$AT2&”</li><li>”&$AU2&”</li><li>”&”UL or ETL Listed?  “&$AZ2&”</li><li>”&”Watts:  “&$BA2&”</li><li>”&”Warranty:  “&$AW2&”</li><li>”&”Warranty Language:<br><br>”&$AX2&”</li></font></b></ul>”))

    If I keep it like it is and there isn’t any data in cell $AQ2 (Item info. bullet #1), then it doesn’t just skip this cell and go onto putting in the info. for the next cell, etc.  It just stops there.

    I’ve also tried the version below, but When I copy & paste this cell info. into my HTML program, I see it is making a “blank” bullet for each cell, even for cells that don’t have any data instead of just skipping those cells:

    =CLEAN(IF($AU2=””,$AP2&”<ul><b><font face=Arial size=2> <li>”&$AQ2&”</li><li>”&$AR2&”</li><li>”&$AS2&”</li><li>”&$AT2&”</li><li>”&”UL or ETL Listed?  “&$AZ2&”</li><li>”&”Watts:  “&$BA2&”</li><li>”&”Warranty:  “&$AW2&”</li><li>”&”Warranty Language:<br><br>”&$AX2&”</li></font></b></ul>”,IF($AW2=””,$AP2&”<ul><b><font face=Arial size=2> <li>”&$AQ2&”</li><li>”&$AR2&”</li><li>”&$AS2&”</li><li>”&$AT2&”</li><li>”&”UL or ETL Listed?  “&$AZ2&”</li><li>”&”Watts:  “&$BA2&”</li><li>”&”Warranty Language:<br><br>”&$AX2&”</li></font></b></ul>”,IF($AX2=””,$AP2&”<ul><b><font face=Arial size=2> <li>”&$AQ2&”</li><li>”&$AR2&”</li><li>”&$AS2&”</li><li>”&$AT2&”</li><li>”&”UL or ETL Listed?  “&$AZ2&”</li><li>”&”Watts:  “&$BA2&”</li><li>”&”Warranty:  “&$AW2&”</li></font></b></ul>”,IF($AZ2=””,$AP2&”<ul><b><font face=Arial size=2> <li>”&$AQ2&”</li><li>”&$AR2&”</li><li>”&$AS2&”</li><li>”&$AT2&”</li><li>”&$AU2&”</li><li>”&”Watts:  “&$BA2&”</li><li>”&”Warranty:  “&$AW2&”</li><li>”&”Warranty Language:<br><br>”&$AX2&”</li></font></b></ul>”,IF($BA2=””,$AP2&”<ul><b><font face=Arial size=2> <li>”&$AQ2&”</li><li>”&$AR2&”</li><li>”&$AS2&”</li><li>”&$AT2&”</li><li>”&$AU2&”</li><li>”&”UL or ETL Listed?  “&$AZ2&”</li><li>”&”Warranty:  “&$AW2&”</li><li>”&”Warranty Language:<br><br>”&$AX2&”</li></font></b></ul>”,$AP2&”<ul><b><font face=Arial size=2> <li>”&$AQ2&”</li><li>”&$AR2&”</li><li>”&$AS2&”</li><li>”&$AT2&”</li><li>”&$AU2&”</li><li>”&”UL or ETL Listed?  “&$AZ2&”</li><li>”&”Watts:  “&$BA2&”</li><li>”&”Warranty:  “&$AW2&”</li><li>”&”Warranty Language:<br><br>”&$AX2&”</li></font></b></ul>”))))))

    I’d like to be able to use the “ISBLANK” thing or something like it to put an argument for each cell, so that if there isn’t anything listed in a cell for “Wattage,” then I can make it also not include this part of the formula:  &”Watts:  “

    I have spent a very long time on this and I cannot figure it out.  Please help!!!  Thank you so much in advance!

All Answers

  • Author
    Replies
    • #2925760

      Clarifications

      by little_dobby ·

      In reply to Excel Help Needed -Add multiple IF statements to a combined string of cells

      Clarifications

    • #2925758

      ???????

      by thumbsup2 ·

      In reply to Excel Help Needed -Add multiple IF statements to a combined string of cells

      I’m looking at the first formula you listed. The simplified version. What cell are you trying to put this formula into?

      edited to add: After rereading your EDITED version of the question, I see you’re trying to concatenate data from multiple cells into one cell so you can access that one cell from your web page. I think you’ll find that it won’t work that way. Your web page will pull in the formula itself, which is the contents of the cell. Excel can display the results of the formula, but the cell still only contains that formula and not what you’re looking for.

      A better way to do it would be to use a programming language like PHP which can pull the contents of many cells, check their contents, concatenate what you want or bullet it if you want.

      Another thing you need to consider is that Excel spreadsheets are NOT a good way of creating a database for web content. You’re going to run into problems with it that way.

      • #2925748

        I don’t know PHP just yet

        by little_dobby ·

        In reply to ???????

        I’m starting with what I know how to do. I use Excel to make changes to product pricing, and I know I need to build the database at the very least before I can have someone help me figure out how to use PHP or ASP and pull from the database. I want to have the database built in Excel, then exported to CSV and uploaded to the site for the site to pull data from.

        I can’t figure out how to paste my Excel formula without this site taking the html and converting it to html instead of text only. So it makes it hard to follow my formula. If you know how I can even post my formula in text instead of html, that would be helpful, too! Thank you!

        • #2925745

          OK, then….

          by thumbsup2 ·

          In reply to I don’t know PHP just yet

          Give me a little time and I’ll work on it for you.

          I presume there are multiple rows of data under the header columns you discribed in your first post and you want one column all the way down through the data to represent the results of concatenated cells in each row. You indicated that the last column used was BA. If you had a forumula in each row of column BB, assuming it’s unused, that should suffice. Right?

          edited to add: What bullet styles are you trying to use?

        • #2925742

          Yes, you are correct

          by little_dobby ·

          In reply to OK, then….

          Yes, column BB would contain the results of the concatenated cells. I already came across some issues with copying the Excel cell and pasting it into my HTML editor, but I found that the problem dealt with Excel retaining digits and spaces that I didn’t want. That is why I put =CLEAN at the front of the formula, which on testing it worked perfectly.

          This formula is mainly for the 600+ products from one vendor who has the product data for the bullets listed in multiple columns. I have a website now where we have manually created every product page for products from different vendors than the one with the multiple columns. I need to convert all those pre-made product pages to have their info. in this same database, so as far as I know, I have to go into Frontpage for every single product page we already have made and copy / paste the product description into a cell and then copy / paste the html code for the product’s bulleted list into the next cell of that row. But that’s another story…

        • #2925721

          Give this a try….

          by thumbsup2 ·

          In reply to I don’t know PHP just yet

          Part of the problem for the formula not finishing the rest of the fields after it encounters a blank cell in AQ is because of your logic. If a blank cell has been encountered, the IF statement performs the TRUE portion of the statement and ignores the ELSE portion (the rest of the formula). So, you need an IF statement to check each and every cell and it all has to be contained within some “shell” formula so they’re all looked at and processed.

          No matter what I tried, there was no way I could use your method of coding the formula (using the & to concatenate multiple lines of a formula). At least, I couldn’t make it work. Someone else might be able to.

          So, pasted below is what I came up with instead. This formula isn’t pretty, but it works (it’s going to look terrible on this forum web page). I used the CONCATENATE formula to enclose everything. So, the logic of each individual cell won’t effect other cell logic. It would be much better to put it into a macro to create your concatenated cell contents, but give this a try and see if it does what you want. Also, I added commas and spaces between each field in the concatenated results so that your web page can read the contents of one cell, yet know where each field begins and ends. I also added an asterisk to indicate empty cells. You can remove that stuff if you don’t want it.

          [b]
          =CONCATENATE(CLEAN($AP2),”, “,IF(ISBLANK($AQ2),”*”,$AQ2),”, “,IF(ISBLANK($AR2),”*”,$AR2),”, “,IF(ISBLANK($AS2),”*”,$AS2),”, “,IF(ISBLANK($AT2),”*”,$AT2),”, “,IF(ISBLANK($AU2),”*”,$AU2),”, “,”UL or ETL Listed? “,IF(ISBLANK($AZ2),”*”,$AZ2),”, “,”Watts: “,IF(ISBLANK($BA2),”*”,$BA2),”, “,”Warranty: “,IF(ISBLANK($AW2),”*”,$AW2),”, “,”Warranty Language: “,IF(ISBLANK($AX2),”*”,$AX2))
          [/b]

          Hint: Make sure you copy from this web page and paste into Notepad first, make sure it’s all on one line, then copy FROM Notepad into the formula bar of Excel. Also, if you’re copying and pasting from Excel into Frontpage, use the same technique. You need to run everything through Notepad first before it’s pasted into Frontpage to strip it of all hidden formatting.

          Hope this helps.

        • #2925711

          Thank you!

          by little_dobby ·

          In reply to Give this a try….

          Thank you very much for your help! I will give it a try and see if I can get your formula to work for my spreadsheet. I really appreciate your help. I’ll let you know how it goes. 😉

          Susie

    • #2925720

      What exactly are you trying to do.

      by thumbsup2 ·

      In reply to Excel Help Needed -Add multiple IF statements to a combined string of cells

      In your original post, it sounded as if you were asking for help with an Excel formula. Now, you’ve edited your post and it appears as if you’re asking for help with FrontPage trying to EXTRACT the data from a spreadsheet and get it to format properly without making blank “bullets” on the web page where there is no data in the database.

      So, I’m very confused. What exactly are you needing help with? Excel, or Frontpage?

      • #2925712

        Both, really, but Excel for this post

        by little_dobby ·

        In reply to What exactly are you trying to do.

        I need to know how to do both of these tasks, but for this post, I was really just asking how to combine cells into one cell while skipping cells that don’t have any data, including skipping the &”Data Header” part for those same cells.

        • #2925710

          re: both really

          by thumbsup2 ·

          In reply to Both, really, but Excel for this post

          Getting data ready to be used in FrontPage is the easy part. Getting FrontPage to do what you want it to do with that data is nearly impossible. Trust me, I’ve used it for years. 😉

          By the way, in your original post, you said: “I’ve also tried the version below, but When I copy & paste this cell info. into my HTML program, I see it is making a “blank” bullet for each cell, even for cells that don’t have any data instead of just skipping those cells”

          FrontPage isn’t designed to make decisions based on blank/non-blank data. In FrontPage, you’re going to get a blank bullet for cells that are blank, no matter what you do. You’ll need programming, such as ASP or PHP, to do this, as well as a server that can handle that programming.

        • #2925701

          Thanks

          by little_dobby ·

          In reply to re: both really

          Well, that’s what we’re aiming for eventually. But I’ve gotta start somewhere, so I’m starting with the database for now… But thank you for your input!

        • #2925643

          If that’s what you want to do eventually….

          by thumbsup2 ·

          In reply to Thanks

          Why bother with concatenating data into one cell in the spreadsheet? You’re going about things completely backwards. You’re eventually going to have to import that data into some sort of database online which will not allow the formulas in the data, then use a programming language to extract the data to be displayed on the web page. By using a language such as PHP, you can extract the data from the real database (not Excel), check to see if the field is blank and make the decision to show a bulleted item or not on the web page based on the contents of each cell.

          Why try to test in FrontPage if you know it’s not going to do what you want it to do and won’t be what you’re going to use for the end result?

          Just get the data ready in Excel and nothing more. There shouldn’t be any kind of formulas in that data. It should be straight data only. Then, start learning how to use the tools that your server (host) will recognize. For example, some servers will provide MySQL for a database and will allow PHP programming. Other servers use SQL and .ASP. FrontPage is only used to manage the files within the “web” and keep both the local copy and the online copy synchronized. It’s not used to do the actual programming.

        • #2926061

          I never answered your question about why I want to bother with this…

          by little_dobby ·

          In reply to If that’s what you want to do eventually….

          I don’t know how long it will take to get a dynamic database-driven site up and running. In the meantime, we want to be able to start selling these 500-700 items from this one vendor on various online selling sites. For that, I needed an item description for one cell, as most selling sites have just one cell of a csv file that you can input info. for the product description. Sorry – I actually had been messing with that formula for so long that I forgot why I needed it! 😛

          Our current web host only has PHP programming, but I’m not set on using them for sure for our updated site.

          Also, to get all the data from our current site (we currently have nearly 800 different product pages that we built manually using FrontPage 2003), I need to extract the product title, description, bullets (some products have 15-20 bullets of info.), image URLs, and if possible, the metatag data I’ve already made. I would then need to input this into this same Excel spreadsheet database I’m building. Do you know of a way to automate pulling data from our current site’s HTML and putting it into an Excel or csv or txt file. There is a lot of info. in the HTML files I don’t need (tables info., includes pages, etc.).

          The only way I know how to do this is to go into FrontPage for every single product page we already have made and copy / paste the product description into a cell in Excel and then copy / paste the html code for the product’s bulleted list into the next cell of that row, and do the same for the images and the metatag data. With nearly 800 htm pages, this will take forever to do it manually. Does anyone know of a way I can automate some or all of this giant task?? I don’t know if I’m allowed to include the URL to our site here or not, so I have left it out. If you need it for reference in helping me figure this out, please let me know. Any info. at all will be greatly appreciated. Thank you!

          Susie

        • #2926055

          re: extracting data from web page to Excel….

          by thumbsup2 ·

          In reply to I never answered your question about why I want to bother with this…

          There is no easy way to do it. As far as I know, without your current web site being run out of a database, you’re going to have to do it one page at a time, one item at a time.

          However, I might be able to help with HOW you get each item out of the site and over to Excel. You can copy the item data itself out of viewing the page in a browser. Don’t do it out of FrontPage. That copies too much “crap”. Paste what you’ve copied directly into Excel. It’s better if the item data is in a table on the web page, but it works (to a point) without it. You might have a little cut-n-pasting to do, depending on how it’s formatted on the page. But it can be done.

          It’s at this point that everything gets really tricky though. What we’ve done is to write custom macro’s (VB) to extract the contents of that pasted data (we call it raw data which has the formatting still in it, colors and all) into another sheet in the same workbook. The data is then clean and in a format that we can export to CSV and import to a MySQL database online. There, I have PHP programming to extract the data from MySQL for use in the pages generated by more PHP programming. The PHP is written using a text editor, but the PHP files are all stored in my FrontPage “web” so I can manage them easily (upload/synchronize). I go through this process at least once a month.

          PHP isn’t all that hard to learn. If you can learn the syntax of that formula you ended up with (Whew We!!!), you can learn the syntax of a programming language. All you need to add is the logic to figure out the flow of the program. Macros (VB) aren’t all that hard either. You can do it, so go for it! 😀

      • #2926076

        I’ve got my formula working perfectly now…

        by little_dobby ·

        In reply to What exactly are you trying to do.

        Thank you very much for your help.

        • #2926059

          Glad you got it working…

          by thumbsup2 ·

          In reply to I’ve got my formula working perfectly now…

          I tried it that way and couldn’t get past the syntax checker. Probably had a quote or three out of place. There’s a lot of quotin’ goin’ on there. LOL!!! 😉

          Anyway, glad you’ve got it.

        • #2926052

          Thank you again

          by little_dobby ·

          In reply to Glad you got it working…

          I couldn’t reply to the last part you wrote about PHP and VB, etc. (it said I had reached the maximum level of replies to that post). The only real reason I wanted to use FrontPage to copy/paste the data was to retain the bulleted list html code (paste it into the formula bar of Excel, not into the cell). I’ll look into what you’re talking about as far as copy/pasting as-is from our site into one Excel worksheet and then pulling it into another worksheet in that same workbook. Thank you again for your help, and for the vote of confidence!! 🙂 Take it easy!

          Susie

        • #2926710

          re: Reached maximum…..

          by thumbsup2 ·

          In reply to Thank you again

          When a thread has reached it’s maximum, just reply to the post above it (up one level) and put in the title who you’re replying to (to save confusion).

          We all deal with the limit on posts, practically on a daily basis. It’s confusing at first to a new comer. But, you get used to it after a while.

Viewing 2 reply threads