+ Reply to Thread
Results 1 to 13 of 13

Formula for IF Column has match in cell post data from A1 in current Cell?

  1. #1
    Registered User
    Join Date
    07-08-2015
    Location
    TX
    MS-Off Ver
    2007
    Posts
    5

    Question Formula for IF Column has match in cell post data from A1 in current Cell?

    I am working with a database of products and I need to match products up with what vehicle they fit.

    I currently use Microsoft Office 2007, but will get a newer version if needed for this formula since it would be detrimental to the project I am working on.

    Example:

    My Vehicles are all listed in in Row 1 so:

    A1 is (Vehicle 1), B1 is (Vehicle 2), C1 is (Vehicle 3), ETC


    The products that fit these vehicles are listed beneath them so:

    A2 is (Product 1) B2 is (Product 5) C2 is (Product 7)
    A3 is (Product 2) B3 is (Product 1) C3 is (Product 3)
    A4 is (Product 3) B4 is (Product 7) C4 is (Product 1)
    ETC


    way on the other end of the spread sheet I have in say column ZX I have all of the products listed so:

    ZX1 (Product 1)
    ZX2 (Product 2)
    ZX3 (Product 3)

    I need a formula in column ZZ that will search for exact matches of (product 1) and anytime it finds a match it needs to post the data from the top of the column the match was found in (the vehicle it fits) into column ZZ if possible it needs to also separate the matches with a , or / or something my database can read as a separation.

    Example:

    ZZ1 found match for ZX1 (product 1) in A2, B3, and C4. So it needs to read the data in A1, B1,and, C1 and post into ZZ1 as (vehicle 1), (vehicle 2), (vehicle 3)
    ZZ2 found match for ZX2 (product 2) in A3 So it needs to read the data in A1 and post into ZZ1 as (vehicle 1),
    ZZ1 found match for ZX1 (product 1) in A4, and C3. So it needs to read the data in A1,and, C1 and post into ZZ1 as (vehicle 1), (vehicle 3)
    ETC

    I am sorry for the long post I am just trying to be clear in what it is I need and I am not good with formulas.

    If I need to post the results in different columns that would work as well, say if the formula would only work for 1 column at a time I could do each column and merge the results later, but we are talking over 20,000 products and 1,000's of vehicles so anything to speed up the process is tremendous.

    Thank you so much for taking the time to read my post and respond.

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Formula for IF Column has match in cell post data from A1 in current Cell?

    Hi, welcome to the forum

    I would suggest that you upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    07-08-2015
    Location
    TX
    MS-Off Ver
    2007
    Posts
    5

    Re: Formula for IF Column has match in cell post data from A1 in current Cell?

    Thanks for the tip. I am attaching a small segment of what I am working with that I made up for this post.

    I would have to post way to large of an excel sheet to post what I am really working with.

    Thanks ahead of time to anyone that helps. :D
    Attached Files Attached Files

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Formula for IF Column has match in cell post data from A1 in current Cell?

    For future reference, upload an excel file next time

    OK, this got a bit messy, and I presume you will have far more than 4 columns of car (I just saw you have 1000's) - which could make it even more messy
    If you could put all the columns into 2 columnS, 1 below the other, with the make/model in A and the part number in B, this would be far easier - let me know if this would be an option?

    What I did was to create a range of helper columns off to the side (1 for each make/model) starting in Z1, and duplicated the headings.
    Then in Z2, copied down and across, I used this...
    =IF(ISNUMBER(MATCH($F2,A:A,0)),Z$1,"")
    copied down and across.

    Then, to pull in the data into column H...
    =INDEX($Z2:$AK2,MATCH(TRUE,INDEX(($Z2:$AK2<>""),0),0))
    copied down

    I noticed, while doing the above, that you have the same part in multiple columns?

    See the attached, it may make more sense

    Also, in the attached, I have added a 2nd sheet, where I put all your data in 2 columns, and returns the make/model that way (just so you can see how that would work)
    Attached Files Attached Files
    Last edited by FDibbins; 07-09-2015 at 02:26 AM.

  5. #5
    Forum Expert cbatrody's Avatar
    Join Date
    04-15-2014
    Location
    Dubai
    MS-Off Ver
    Microsoft Office 365 ProPlus
    Posts
    2,136

    Re: Formula for IF Column has match in cell post data from A1 in current Cell?

    OR,

    In H2 try the following formula:

    Please Login or Register  to view this content.
    See the attached file.
    Attached Files Attached Files

  6. #6
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Formula for IF Column has match in cell post data from A1 in current Cell?

    cbatrody, I had considered something like that, but that will run out of IF's and/or become impossible to manage, considering 1000's of models...
    but we are talking over 20,000 products and 1,000's of vehicles so anything to speed up the process is tremendous.

  7. #7
    Forum Expert cbatrody's Avatar
    Join Date
    04-15-2014
    Location
    Dubai
    MS-Off Ver
    Microsoft Office 365 ProPlus
    Posts
    2,136

    Re: Formula for IF Column has match in cell post data from A1 in current Cell?

    Quote Originally Posted by FDibbins View Post
    cbatrody, I had considered something like that, but that will run out of IF's and/or become impossible to manage, considering 1000's of models...
    You are right Ford, the formula cannot be managed if the number of models are over 1000's.

    I guess Pivot Table would be an easier option if the data is split into 2 columns - "Make" & "Part Number"
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    07-08-2015
    Location
    TX
    MS-Off Ver
    2007
    Posts
    5

    Re: Formula for IF Column has match in cell post data from A1 in current Cell?

    Cbatrody
    See the attached file.
    Attached Files Attached Files
    File Type: xlsx formula-for-if-column-has-match.xlsx‎ (12.6 KB, 3 views) Download
    Yes, I think this is what I am looking for, but let me explain a bit more on how many results will be found to make sure this will work with the data on the larger end. The database I am going to be using is already built on product, but the vehicle side of things is only theoretical at the moment. At least a few weeks worth of work.

    If needed I can break down the database and do a smaller amount of vehicles at a time for example I could set up the database as vintage cars, set up one for modern cars, and another for Trucks since the matching products won't be the same from these categories. I am not sure on exact numbers, but this would put me on a much lower range for the formula. (Best guess is no more than 500 -600 vehicles for each database.)

    There are 1000's of vehicles and over 20,000 products, but each of the 20,000 product's would match at maximum around say 100 vehicles. This is on the very high end of the spectrum.

    Most products I would say match to no more than 50 vehicles.


    Sorry, for the use of a non Excel upload. Doing this from home to make sure this is going to be possible before I bring it to my boss and my only MS Office copy is on my work computer.


    Everyone that has replied thank you so much. I was honestly surprised to see people responding with answers so quickly, you guys rock!
    Last edited by pbr187; 07-10-2015 at 12:07 AM.

  9. #9
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Formula for IF Column has match in cell post data from A1 in current Cell?

    Did you see my question regarding the feasibility of having a long, 2-column list for all your data?

  10. #10
    Registered User
    Join Date
    07-08-2015
    Location
    TX
    MS-Off Ver
    2007
    Posts
    5

    Re: Formula for IF Column has match in cell post data from A1 in current Cell?

    Oops, sorry I forgot to respond to that.

    I could definitely put all of the products into one column and all the vehicles in another.

    I am assuming you mean column A will be the vehicle with 1 vehicle make and model per cell and column B would be all the matching products in one cell next to the vehicle they fit and separate the products with , or ; or something?

  11. #11
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Formula for IF Column has match in cell post data from A1 in current Cell?

    Yes, that is exactly what I meant

    If you look at the extra sheet that I added in my upload, you will see what I meant

  12. #12
    Registered User
    Join Date
    07-08-2015
    Location
    TX
    MS-Off Ver
    2007
    Posts
    5

    Re: Formula for IF Column has match in cell post data from A1 in current Cell?

    Ah ok my bad, I missed the second sheet when I looked at it.

    So unless I am mistaken, the formula on that second sheet is only able to pick one match per product for a vehicle?

    Each column cell for the vehicle could be just one vehicle example:

    A1 could be 1 vehicle, but the products it match would be a lot more than that in B1.

    Also the when the formula looks for a match to the product it would need to match anywhere between 40 - 100 vehicles.

  13. #13
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Formula for IF Column has match in cell post data from A1 in current Cell?

    I have revised my file and given you 2 options - 1 with products going down the column and vehicles going across (yellow) and another 1 with the products going across the top and the vehicles going down (Blue). See which 1 suites you best

    Note that the both use an ARRAY formula
    products down the column...
    E2=IFERROR(INDEX($A$2:$A$94,SMALL(IF($B$2:$B$94=$D2,ROW($A$2:$A$94)-1),COLUMNS($A$1:A1))),"")
    copied down and across

    products going across the top...
    I2=IFERROR(INDEX($A$2:$A$94,SMALL(IF($B$2:$B$94=I$1,ROW($A$2:$A$94)-1),ROWS($A$1:A1))),"")
    copied down and across.

    As I said, these are both ARRAY formulas...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.
    Attached Files Attached Files

+ 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] Formula to match diff column with current data
    By mark888 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 03-20-2015, 02:38 PM
  2. Replies: 5
    Last Post: 09-12-2012, 07:00 PM
  3. [SOLVED] Set variable to active cell's current Data validaion formula?
    By magicbob007 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-21-2012, 05:54 PM
  4. Replies: 1
    Last Post: 04-02-2008, 12:03 AM
  5. [SOLVED] Can I get current #row or #column in cell formula?
    By ryany in forum Excel General
    Replies: 2
    Last Post: 02-13-2006, 07:30 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