+ Reply to Thread
Results 1 to 8 of 8

Formula needed to combine, not delete, multiple rows if duplicates found in column A

  1. #1
    Registered User
    Join Date
    12-30-2011
    Location
    Mobile, al
    MS-Off Ver
    Excel 2013
    Posts
    40

    Formula needed to combine, not delete, multiple rows if duplicates found in column A

    In the attached spreadsheet, page 2 (data), you will see same item number in column A. Each row is different do to the different mfg's having same item number with different cost.
    Page 1 (Form) has formulas in columns E and J (Thanks to you experts) that extracts data from page 2. Now that I've added another mfg's information the first "true" result is often an empty cell.
    If I can combine columns with like item numbers, my problem will be solved.
    I also have 2 more mfg's to add when their information is sent to me. So this formula I seek will save me a ton of time. Thank you!

    Or manipulate formula on page 1 to get desired results. Open to anything. Thanks
    Attached Files Attached Files
    Last edited by jondon; 02-22-2015 at 04:57 PM.

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    21,079

    Re: Formula needed to combine, not delete, multiple rows if duplicates found in column A

    VLOOKUP works by looking for a unique value and returns the corresponding values from that row. In your case you no longer have unique values in column A, so you need to combine them into one row. However, the formula that I gave you uses the values on the top row of the table to determine from which column the corresponding data should be returned, but you now have different identifiers along that top row, so you need to sort out a method whereby you can set the appropriate identifier in the Form sheet.

    I suggest that you add the other two manufacturer's details to the Data sheet as best you can, and then post that updated file so we can see how we might help you with the bigger picture.

    Hope this helps.

    Pete

  3. #3
    Registered User
    Join Date
    12-30-2011
    Location
    Mobile, al
    MS-Off Ver
    Excel 2013
    Posts
    40

    Re: Formula needed to combine, not delete, multiple rows if duplicates found in column A

    Would specifying the mfg on page 1 allow you to accomplish this by adding to current formula? Would give you 3 known variables. Let me know and I will add other info to the sheet and post it when I have all the other mfg's information as suggested. In the meantime do I leave this post open. Thank you Pete.

    I did add more identifiers to the top row but also adjusted formula to look in those cells. I think I did so correctly.

    It may also be better to create one order form for each mfg. Your thoughts?
    Last edited by jondon; 02-22-2015 at 06:11 PM.

  4. #4
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    21,079

    Re: Formula needed to combine, not delete, multiple rows if duplicates found in column A

    Leave this thread open - just come back to it when you have another file to attach, and that way I'll get the email notification of a new post.

    I don't really know what you want to do with this file, so I can't advise whether it would be better to have one form for each manufacturer - how many manufacturers do you have? It is possible to have one form which has a drop-down to enable you to select the manufacturer (you need to maintain a list somewhere).

    Let's see what it looks like when you've added the new data.

    Pete

  5. #5
    Registered User
    Join Date
    12-30-2011
    Location
    Mobile, al
    MS-Off Ver
    Excel 2013
    Posts
    40

    Re: Formula needed to combine, not delete, multiple rows if duplicates found in column A

    Thanks Pete. I think it may be better to use original as template for each mfg. As time goes on it could get really large. The real spreadsheet (order form) has 223 rows of data and your formula works perfectly. Continuing to add to it may create issues. Just adding those 2 mfg's has already giving it 840 rows of data and some for reason all these mfg's have a lot of the same item numbers. Thanks for all your help, it is greatly appreciated.

  6. #6
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    21,079

    Re: Formula needed to combine, not delete, multiple rows if duplicates found in column A

    Okay, I'm signing off now, but I'll check back in the morning.

    Pete

  7. #7
    Registered User
    Join Date
    12-30-2011
    Location
    Mobile, al
    MS-Off Ver
    Excel 2013
    Posts
    40

    Re: Formula needed to combine, not delete, multiple rows if duplicates found in column A

    Looks as if we were posting at the same time. I will leave it open and get back to when I have all the information I'm expecting.

  8. #8
    Registered User
    Join Date
    12-30-2011
    Location
    Mobile, al
    MS-Off Ver
    Excel 2013
    Posts
    40

    Re: Formula needed to combine, not delete, multiple rows if duplicates found in column A

    Petes first formula was perfect. One sheet per mfg works perfect. No reason to try to combine them.

+ 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] If Duplicates Are Found in Column A, then consolidate Rows.
    By EnigmaMatter in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 02-11-2015, 12:50 PM
  2. Macro to delete multiple rows below that that is found
    By andy.k in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-05-2013, 07:45 PM
  3. Replies: 3
    Last Post: 11-08-2012, 09:29 PM
  4. [SOLVED] To Delete the specific rows when blank is found on column A
    By ddiicc in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-05-2005, 01:05 AM
  5. Delete Rows when Blanks are found in Column A:A
    By ddiicc in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-04-2005, 06:05 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