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

**jondon** · 02-22-2015, 04:43 PM

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

**Pete_UK** · 02-22-2015, 05:21 PM

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

**jondon** · 02-22-2015, 05:40 PM

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?

**Pete_UK** · 02-22-2015, 06:34 PM

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

**jondon** · 02-22-2015, 06:44 PM

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.

**Pete_UK** · 02-22-2015, 06:46 PM

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

Pete

**jondon** · 02-22-2015, 06:46 PM

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.

**jondon** · 03-13-2015, 05:34 PM

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

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

LinkBack

Thread Tools

Rate This Thread

Display

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

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

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

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

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

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

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

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

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] If Duplicates Are Found in Column A, then consolidate Rows.

Macro to delete multiple rows below that that is found

Combine rows by adding amounts found in column d for duplicate names in Column a

[SOLVED] To Delete the specific rows when blank is found on column A

Delete Rows when Blanks are found in Column A:A

Bookmarks

Bookmarks

Posting Permissions