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

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

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
<table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">Item #</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">Vendor</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">Department</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">Item Name</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">Item Description</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">Attribute</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">Size</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">Inv Qty</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">Sold Qty</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">Received Qty</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;border-top: 1px solid black;;">1</td><td style="border-top: 1px solid black;;">HomArt</td><td style="border-top: 1px solid black;;">Utility</td><td style="border-top: 1px solid black;;">Matches - Stag Deer</td><td style="border-top: 1px solid black;;"></td><td style="border-top: 1px solid black;;"></td><td style="border-top: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;;">20</td><td style="text-align: right;border-top: 1px solid black;;">1</td><td style="text-align: right;border-top: 1px solid black;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">1</td><td style=";">HomArt</td><td style=";">Miscellaneous</td><td style=";">Matches - Stag Deer</td><td style=";"></td><td style=";"></td><td style=";"></td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">20</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">75</td><td style=";">LAFCO New York</td><td style=";"></td><td style=";">Lake House Candle</td><td style=";">Water Hyacinth</td><td style=";"></td><td style=";"></td><td style="text-align: right;;">0</td><td style="text-align: right;;">1</td><td style="text-align: right;;">8</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">75</td><td style=";">LAFCO New York</td><td style=";">Candles & Fragrance</td><td style=";">LAFCO Candle</td><td style=";">Lake House</td><td style=";">Water Hyac</td><td style=";"></td><td style="text-align: right;;">7</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;">104</td><td style=";">Creative Co-Op, Inc.</td><td style=";">Wall Decor</td><td style=";">MDF Wall Plaque w/Lake Image</td><td style=";">4 Styles</td><td style=";"></td><td style=";">18-1/8" Long</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;">104</td><td style=";">Creative Co-Op, Inc.</td><td style=";"></td><td style=";">MDF Wall Plaque w/Lake Image</td><td style=";">4 Styles</td><td style=";"></td><td style=";">18-1/8" Long</td><td style="text-align: right;;">0</td><td style="text-align: right;;">4</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;">104</td><td style=";">Creative Co-Op, Inc.</td><td style=";"></td><td style=";">MDF Wall Plaque</td><td style=";"></td><td style=";"></td><td style=";">18-1/8" Long</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">4</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;">625</td><td style=";">Bella Cucina</td><td style=";">Food</td><td style=";">Antipasti</td><td style=";">Carmelized Onions, Fennel & G...</td><td style=";"></td><td style=";">6 oz</td><td style="text-align: right;;">0</td><td style="text-align: right;;">2</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;;">625</td><td style=";">Bella Cucina</td><td style=";">Food</td><td style=";">Antipasti</td><td style=";">Carmelized Onions, Fennel & Golden Raisins</td><td style=";">OnFenRais</td><td style=";">6 oz</td><td style="text-align: right;;">10</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: right;;">625</td><td style=";">Bella Cucina</td><td style=";">Food</td><td style=";">Carmelized Onions,Fennel,Raisin</td><td style=";">Carmelized Onions, Fennel & G...</td><td style=";"></td><td style=";">6 oz</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">12</td></tr></tbody></table>

Desired Output
<table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">Item #</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">Vendor</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">Department</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">Item Name</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">Item Description</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">Attribute</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">Size</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">Inv Qty</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">Sold Qty</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">Received Qty</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;border-top: 1px solid black;;">1</td><td style="border-top: 1px solid black;;">HomArt</td><td style="border-top: 1px solid black;;">Utility</td><td style="border-top: 1px solid black;;">Matches - Stag Deer</td><td style="border-top: 1px solid black;;"></td><td style="border-top: 1px solid black;;"></td><td style="border-top: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;;">20</td><td style="text-align: right;border-top: 1px solid black;;">1</td><td style="text-align: right;border-top: 1px solid black;;">21</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">75</td><td style=";">LAFCO New York</td><td style=";">Candles & Fragrance</td><td style=";">LAFCO Candle</td><td style=";">Lake House</td><td style=";">Water Hyac</td><td style=";"></td><td style="text-align: right;;">7</td><td style="text-align: right;;">1</td><td style="text-align: right;;">8</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">104</td><td style=";">Creative Co-Op, Inc.</td><td style=";">Wall Decor</td><td style=";">MDF Wall Plaque w/Lake Image</td><td style=";">4 Styles</td><td style=";"></td><td style=";">18-1/8" Long</td><td style="text-align: right;;">0</td><td style="text-align: right;;">4</td><td style="text-align: right;;">4</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">625</td><td style=";">Bella Cucina</td><td style=";">Food</td><td style=";">Antipasti</td><td style=";">Carmelized Onions, Fennel & Golden Raisins</td><td style=";">OnFenRais</td><td style=";">6 oz</td><td style="text-align: right;;">10</td><td style="text-align: right;;">2</td><td style="text-align: right;;">12</td></tr></tbody></table>

Thanks for your patience!!!
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,130,187
Messages
5,640,693
Members
417,161
Latest member
Devon150

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