Remove duplicate rows, after sum & concatenate 2 separate columns

Sasha2016
Sasha2016 used Ask the Experts™
on
Hello,
I'm in need of a code that would look for duplicate rows; sum info in one column, and concatenate info in different column before deleting duplicated rows.
Only 3 cells are "criteria" for considering row "duplicated"
Spreadsheet has several "categories" separated by Glass Type description (see column A), so code needs to look in between blank rows in columns B:K
Sample spreadsheet with more details is attached, but here's the short version:
If "OD"   "Width" and "Height"  are ALL identical in multiple rows, keep only one row, but summarize QTY and concatenate "TAG" info:

  QTY     OD       TAG      WIDTH       HEIGHT  
    5      1 3/8"     006          26              50
  10      1 3/8"     008          26              50
Need to show:
   15    1 3/8"   006;008      26             50

These rows with duplicate data can be anywhere on the sheet  (potentially up to 1500 rows of data)
IMPORTANT: data is in split in different categories, so in my plain English code should do following:
start at the bottom of the sheet - go up until blank cell in column "B" (i.e B200)- run the code - continue from B200 up, until next blank cell is reached - run the code....and so on.

Any help would be much appreciated.
Thanks!
Sasha
Sample-sheet.xlsx
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Roy CoxGroup Finance Manager

Commented:
Actually, you can select a whole ListColumn and drag & drop.

Author

Commented:
Hi Roy,
thanks for looking into this.
I'm creating a macro workbook where user's manual input needs to stay at minimum.
The sample sheet is about half way done...idea is for users to open the blank sheet and click a button...Data would be copied from "template" sheet, sorted and it adds a bunch of additional information. Point being, I can't rely on users to remember (or know how) to manually modify data.
That being said, could you elaborate on "drag and drop"? What column(s)?
Maybe I can record a macro for that.

Thanks,
Sasha
Roy CoxGroup Finance Manager

Commented:
Sorry, I posted the wrong comment, please ignore it.

I'll take a look at your sheet as soon as I get a minute
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
OK, thanks.

FYI, I can add columns in order to run formulas, if that helps. Concatenate 3 critical cells in order to get unique values (OD&width&height), but that still leaves me with the issue of looking in between empty rows only....and those are always in different rows.
Roy CoxGroup Finance Manager

Commented:
The problem would be having to loop through checking in code so a helper column would be a help. You can also use COUNTBLANK to check for empty cells
It took me a while with a bunch of formulas, and helper columns, but I got it working. :)
A lot of COUNTIFs / MATCH etc.... it takes a while to run macros, but it's working.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial