Merging Rows with Duplicate Data and Unique Text

[k10sat]

I exported 6000+ rows of data to Excel from my Point of Sale program. As you can see, nearly every item has duplicate entries. Any change in POS (item name, department, description, etc.) generates a separate line item. More changes = more lines.

Is it possible to merge the rows and combine my sales & inventory data? Item # is my unique identifier.

Any text is always better than none, and a specific Department name is always better than "Miscellaneous". Truthfully, though, I'll take what I can get. Anything beats manually sifting through 6,000 lines of data!

I know a pivot table will give me a clean report if I'm looking only at item # and quantities. It gets messy, though, when I add in the text fields.

I submit this report weekly so any suggestions would be greatly appreciated!

 

[hiker95]

k10sat,

1. What version of Excel and Windows are you using?

2. Are you using a PC or a Mac?


You are posting a picture. This means that if this was a problem where one needed to use your data, anyone trying to help you would have to enter the data manually. That makes no sense.


In order to get it right the first time:

Can you post a screenshot of the actual raw data worksheet?

And, can you post a screenshot of the worksheet results (manually formatted by you) that you are looking for?

To post your data, you can download and install one of the following two programs:
1. MrExcel HTMLMaker20101230
https://onedrive.live.com/?cid=8cffdec0ce27e813&sc=documents&id=8CFFDEC0CE27E813!189

Installation instructions here:
http://www.mrexcel.com/forum/board-announcements/515787-forum-posting-guidelines.html#post2545970

2. Excel Jeanie
Download


If you are not able to give us screenshots:
You can upload your workbook to Box Net,
sensitive data changed
mark the workbook for sharing
and provide us with a link to your workbook.

 

[k10sat]

Sorry about that. I'm using Excel 2010 on Windows.

Raw Data

Book1
	A	B	C	D	E	F	G	H	I	J
1	Item #	Vendor	Department	Item Name	Item Description	Attribute	Size	Inv Qty	Sold Qty	Received Qty
2	1	HomArt	Utility	Matches - Stag Deer				20	1	1
3	1	HomArt	Miscellaneous	Matches - Stag Deer				0	0	20
4	75	LAFCO New York		Lake House Candle	Water Hyacinth			0	1	8
5	75	LAFCO New York	Candles & Fragrance	LAFCO Candle	Lake House	Water Hyac		7	0	0
6	104	Creative Co-Op, Inc.	Wall Decor	MDF Wall Plaque w/Lake Image	4 Styles		18-1/8" Long	0	0	0
7	104	Creative Co-Op, Inc.		MDF Wall Plaque w/Lake Image	4 Styles		18-1/8" Long	0	4	0
8	104	Creative Co-Op, Inc.		MDF Wall Plaque			18-1/8" Long	0	0	4
9	625	Bella Cucina	Food	Antipasti	Carmelized Onions, Fennel & G...		6 oz	0	2	0
10	625	Bella Cucina	Food	Antipasti	Carmelized Onions, Fennel & Golden Raisins	OnFenRais	6 oz	10	0	0
11	625	Bella Cucina	Food	Carmelized Onions,Fennel,Raisin	Carmelized Onions, Fennel & G...		6 oz	0	0	12
Sheet1

Desired Output

Book1
	A	B	C	D	E	F	G	H	I	J
1	Item #	Vendor	Department	Item Name	Item Description	Attribute	Size	Inv Qty	Sold Qty	Received Qty
2	1	HomArt	Utility	Matches - Stag Deer				20	1	21
3	75	LAFCO New York	Candles & Fragrance	LAFCO Candle	Lake House	Water Hyac		7	1	8
4	104	Creative Co-Op, Inc.	Wall Decor	MDF Wall Plaque w/Lake Image	4 Styles		18-1/8" Long	0	4	4
5	625	Bella Cucina	Food	Antipasti	Carmelized Onions, Fennel & Golden Raisins	OnFenRais	6 oz	10	2	12
Sheet1

Thanks for your patience!!!