Merging Rows with Duplicate Data and Unique Text

k10sat

New Member
Joined
Sep 2, 2014
Messages
4
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!

sample_zpse62ba548.jpg
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

hiker95

Well-known Member
Joined
Apr 8, 2009
Messages
17,649
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

New Member
Joined
Sep 2, 2014
Messages
4
Sorry about that. I'm using Excel 2010 on Windows.

Raw Data
Book1
ABCDEFGHIJ
1Item #VendorDepartmentItem NameItem DescriptionAttributeSizeInv QtySold QtyReceived Qty
21HomArtUtilityMatches - Stag Deer2011
31HomArtMiscellaneousMatches - Stag Deer0020
475LAFCO New YorkLake House CandleWater Hyacinth018
575LAFCO New YorkCandles & FragranceLAFCO CandleLake HouseWater Hyac700
6104Creative Co-Op, Inc.Wall DecorMDF Wall Plaque w/Lake Image4 Styles18-1/8" Long000
7104Creative Co-Op, Inc.MDF Wall Plaque w/Lake Image4 Styles18-1/8" Long040
8104Creative Co-Op, Inc.MDF Wall Plaque18-1/8" Long004
9625Bella CucinaFoodAntipastiCarmelized Onions, Fennel & G...6 oz020
10625Bella CucinaFoodAntipastiCarmelized Onions, Fennel & Golden RaisinsOnFenRais6 oz1000
11625Bella CucinaFoodCarmelized Onions,Fennel,RaisinCarmelized Onions, Fennel & G...6 oz0012
Sheet1


Desired Output
Book1
ABCDEFGHIJ
1Item #VendorDepartmentItem NameItem DescriptionAttributeSizeInv QtySold QtyReceived Qty
21HomArtUtilityMatches - Stag Deer20121
375LAFCO New YorkCandles & FragranceLAFCO CandleLake HouseWater Hyac718
4104Creative Co-Op, Inc.Wall DecorMDF Wall Plaque w/Lake Image4 Styles18-1/8" Long044
5625Bella CucinaFoodAntipastiCarmelized Onions, Fennel & Golden RaisinsOnFenRais6 oz10212
Sheet1


Thanks for your patience!!!
 
Last edited:
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,652
Messages
5,832,928
Members
430,175
Latest member
Sheenamarie

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top