Minimizing Rows of with similar data

I am using Excel 2007.

I have 3 columns A, B, and C such as below:

TU10-10	TU10	Tungsten Satin Wedding Band
TU10-10.5	TU10	Tungsten Satin Wedding Band
TU10-11	TU10	Tungsten Satin Wedding Band
TU10-11.5	TU10	Tungsten Satin Wedding Band
TU10-12	TU10	Tungsten Satin Wedding Band
TU10-12.5	TU10	Tungsten Satin Wedding Band
TU10-13	TU10	Tungsten Satin Wedding Band
TU10-6	TU10	Tungsten Satin Wedding Band
TU10-6.5	TU10	Tungsten Satin Wedding Band
TU10-7	TU10	Tungsten Satin Wedding Band
TU10-7.5	TU10	Tungsten Satin Wedding Band
TU10-8	TU10	Tungsten Satin Wedding Band
TU10-8.5	TU10	Tungsten Satin Wedding Band
TU10-9	TU10	Tungsten Satin Wedding Band
TU10-9.5	TU10	Tungsten Satin Wedding Band
TU12-10	TU12	Tungsten Brushed Wedding Band
TU12-10.5	TU12	Tungsten Brushed Wedding Band
TU12-11	TU12	Tungsten Brushed Wedding Band
TU12-11.5	TU12	Tungsten Brushed Wedding Band
TU12-12	TU12	Tungsten Brushed Wedding Band
TU12-12.5	TU12	Tungsten Brushed Wedding Band
TU12-13	TU12	Tungsten Brushed Wedding Band
TU12-6	TU12	Tungsten Brushed Wedding Band
TU12-6.5	TU12	Tungsten Brushed Wedding Band
TU12-7	TU12	Tungsten Brushed Wedding Band
TU12-7.5	TU12	Tungsten Brushed Wedding Band
TU12-8	TU12	Tungsten Brushed Wedding Band
TU12-8.5	TU12	Tungsten Brushed Wedding Band
TU12-9	TU12	Tungsten Brushed Wedding Band
TU12-9.5	TU12	Tungsten Brushed Wedding Band
		
I have over 12000 rows with similar data for multiple products.  I need to 
minimize the rows so that I have only one row for each product(such as one 
row for TU10 and one row for TU12)  I need only one description(column C) per 
item.  I do not need column A at all.

For instance, instead of the example above with multiple rows for the 2 
products in column B(TU10 and TU12) I need only 2 rows and 2 columns.  For 
example:

TU10  Tungsten Satin Wedding Band
TU12  Tungsten Brushed Wedding Band

Any thoughts on a formula to accomplish my task?

Thank you.
0
Utf
12/9/2009 4:13:03 PM
excel.newusers 15348 articles. 2 followers. Follow

3 Replies
721 Views

Similar Articles

[PageSpeed] 17

On Wed, 9 Dec 2009 08:13:03 -0800, urlocaljeweler
<urlocaljeweler@discussions.microsoft.com> wrote:

>I am using Excel 2007.
>
>I have 3 columns A, B, and C such as below:
>
>TU10-10	TU10	Tungsten Satin Wedding Band
>TU10-10.5	TU10	Tungsten Satin Wedding Band
>TU10-11	TU10	Tungsten Satin Wedding Band
>TU10-11.5	TU10	Tungsten Satin Wedding Band
>TU10-12	TU10	Tungsten Satin Wedding Band
>TU10-12.5	TU10	Tungsten Satin Wedding Band
>TU10-13	TU10	Tungsten Satin Wedding Band
>TU10-6	TU10	Tungsten Satin Wedding Band
>TU10-6.5	TU10	Tungsten Satin Wedding Band
>TU10-7	TU10	Tungsten Satin Wedding Band
>TU10-7.5	TU10	Tungsten Satin Wedding Band
>TU10-8	TU10	Tungsten Satin Wedding Band
>TU10-8.5	TU10	Tungsten Satin Wedding Band
>TU10-9	TU10	Tungsten Satin Wedding Band
>TU10-9.5	TU10	Tungsten Satin Wedding Band
>TU12-10	TU12	Tungsten Brushed Wedding Band
>TU12-10.5	TU12	Tungsten Brushed Wedding Band
>TU12-11	TU12	Tungsten Brushed Wedding Band
>TU12-11.5	TU12	Tungsten Brushed Wedding Band
>TU12-12	TU12	Tungsten Brushed Wedding Band
>TU12-12.5	TU12	Tungsten Brushed Wedding Band
>TU12-13	TU12	Tungsten Brushed Wedding Band
>TU12-6	TU12	Tungsten Brushed Wedding Band
>TU12-6.5	TU12	Tungsten Brushed Wedding Band
>TU12-7	TU12	Tungsten Brushed Wedding Band
>TU12-7.5	TU12	Tungsten Brushed Wedding Band
>TU12-8	TU12	Tungsten Brushed Wedding Band
>TU12-8.5	TU12	Tungsten Brushed Wedding Band
>TU12-9	TU12	Tungsten Brushed Wedding Band
>TU12-9.5	TU12	Tungsten Brushed Wedding Band
>		
>I have over 12000 rows with similar data for multiple products.  I need to 
>minimize the rows so that I have only one row for each product(such as one 
>row for TU10 and one row for TU12)  I need only one description(column C) per 
>item.  I do not need column A at all.
>
>For instance, instead of the example above with multiple rows for the 2 
>products in column B(TU10 and TU12) I need only 2 rows and 2 columns.  For 
>example:
>
>TU10  Tungsten Satin Wedding Band
>TU12  Tungsten Brushed Wedding Band
>
>Any thoughts on a formula to accomplish my task?
>
>Thank you.


Select columns B and C, then on the Data tab, in the Data Tools group,
click Remove Duplicates.

Hope this helps / Lars-�ke
0
Lars
12/9/2009 5:36:56 PM
Assuming the tu10 and tu12 are in a separate column then use  DATA-DATA 
TOOLS-REMOVE DUPLICATES tool.

Delete col a

"urlocaljeweler" wrote:

> I am using Excel 2007.
> 
> I have 3 columns A, B, and C such as below:
> 
> TU10-10	TU10	Tungsten Satin Wedding Band
> TU10-10.5	TU10	Tungsten Satin Wedding Band
> TU10-11	TU10	Tungsten Satin Wedding Band
> TU10-11.5	TU10	Tungsten Satin Wedding Band
> TU10-12	TU10	Tungsten Satin Wedding Band
> TU10-12.5	TU10	Tungsten Satin Wedding Band
> TU10-13	TU10	Tungsten Satin Wedding Band
> TU10-6	TU10	Tungsten Satin Wedding Band
> TU10-6.5	TU10	Tungsten Satin Wedding Band
> TU10-7	TU10	Tungsten Satin Wedding Band
> TU10-7.5	TU10	Tungsten Satin Wedding Band
> TU10-8	TU10	Tungsten Satin Wedding Band
> TU10-8.5	TU10	Tungsten Satin Wedding Band
> TU10-9	TU10	Tungsten Satin Wedding Band
> TU10-9.5	TU10	Tungsten Satin Wedding Band
> TU12-10	TU12	Tungsten Brushed Wedding Band
> TU12-10.5	TU12	Tungsten Brushed Wedding Band
> TU12-11	TU12	Tungsten Brushed Wedding Band
> TU12-11.5	TU12	Tungsten Brushed Wedding Band
> TU12-12	TU12	Tungsten Brushed Wedding Band
> TU12-12.5	TU12	Tungsten Brushed Wedding Band
> TU12-13	TU12	Tungsten Brushed Wedding Band
> TU12-6	TU12	Tungsten Brushed Wedding Band
> TU12-6.5	TU12	Tungsten Brushed Wedding Band
> TU12-7	TU12	Tungsten Brushed Wedding Band
> TU12-7.5	TU12	Tungsten Brushed Wedding Band
> TU12-8	TU12	Tungsten Brushed Wedding Band
> TU12-8.5	TU12	Tungsten Brushed Wedding Band
> TU12-9	TU12	Tungsten Brushed Wedding Band
> TU12-9.5	TU12	Tungsten Brushed Wedding Band
> 		
> I have over 12000 rows with similar data for multiple products.  I need to 
> minimize the rows so that I have only one row for each product(such as one 
> row for TU10 and one row for TU12)  I need only one description(column C) per 
> item.  I do not need column A at all.
> 
> For instance, instead of the example above with multiple rows for the 2 
> products in column B(TU10 and TU12) I need only 2 rows and 2 columns.  For 
> example:
> 
> TU10  Tungsten Satin Wedding Band
> TU12  Tungsten Brushed Wedding Band
> 
> Any thoughts on a formula to accomplish my task?
> 
> Thank you.
0
Utf
12/9/2009 6:11:01 PM
Select columns A:C

Data>Filter and Sort>Advanced.

List Range  A:C 

Criteria Range  B:B

Filter in place and checkmark "unique records"

If you don't have a title row, you will see two "TU10"'s


Gord Dibben  MS Excel MVP


On Wed, 9 Dec 2009 08:13:03 -0800, urlocaljeweler
<urlocaljeweler@discussions.microsoft.com> wrote:

>I am using Excel 2007.
>
>I have 3 columns A, B, and C such as below:
>
>TU10-10	TU10	Tungsten Satin Wedding Band
>TU10-10.5	TU10	Tungsten Satin Wedding Band
>TU10-11	TU10	Tungsten Satin Wedding Band
>TU10-11.5	TU10	Tungsten Satin Wedding Band
>TU10-12	TU10	Tungsten Satin Wedding Band
>TU10-12.5	TU10	Tungsten Satin Wedding Band
>TU10-13	TU10	Tungsten Satin Wedding Band
>TU10-6	TU10	Tungsten Satin Wedding Band
>TU10-6.5	TU10	Tungsten Satin Wedding Band
>TU10-7	TU10	Tungsten Satin Wedding Band
>TU10-7.5	TU10	Tungsten Satin Wedding Band
>TU10-8	TU10	Tungsten Satin Wedding Band
>TU10-8.5	TU10	Tungsten Satin Wedding Band
>TU10-9	TU10	Tungsten Satin Wedding Band
>TU10-9.5	TU10	Tungsten Satin Wedding Band
>TU12-10	TU12	Tungsten Brushed Wedding Band
>TU12-10.5	TU12	Tungsten Brushed Wedding Band
>TU12-11	TU12	Tungsten Brushed Wedding Band
>TU12-11.5	TU12	Tungsten Brushed Wedding Band
>TU12-12	TU12	Tungsten Brushed Wedding Band
>TU12-12.5	TU12	Tungsten Brushed Wedding Band
>TU12-13	TU12	Tungsten Brushed Wedding Band
>TU12-6	TU12	Tungsten Brushed Wedding Band
>TU12-6.5	TU12	Tungsten Brushed Wedding Band
>TU12-7	TU12	Tungsten Brushed Wedding Band
>TU12-7.5	TU12	Tungsten Brushed Wedding Band
>TU12-8	TU12	Tungsten Brushed Wedding Band
>TU12-8.5	TU12	Tungsten Brushed Wedding Band
>TU12-9	TU12	Tungsten Brushed Wedding Band
>TU12-9.5	TU12	Tungsten Brushed Wedding Band
>		
>I have over 12000 rows with similar data for multiple products.  I need to 
>minimize the rows so that I have only one row for each product(such as one 
>row for TU10 and one row for TU12)  I need only one description(column C) per 
>item.  I do not need column A at all.
>
>For instance, instead of the example above with multiple rows for the 2 
>products in column B(TU10 and TU12) I need only 2 rows and 2 columns.  For 
>example:
>
>TU10  Tungsten Satin Wedding Band
>TU12  Tungsten Brushed Wedding Band
>
>Any thoughts on a formula to accomplish my task?
>
>Thank you.

0
Gord
12/9/2009 6:22:44 PM
Reply:

Similar Artilces:

Sorting Data 11-25-09
If I have two columns, Column A which has Dates and Column B which has numerical values for that date. And I have several dates that I need to take that specific date and the 20 days before/after. What formula can I use that will highlight the range. Suppose your target date is in D1. Highlight columns A and B so that A1 is the active cell, and click on Format | Conditional Formatting and then choose Formula Is rather than Cell Value Is in the first panel of the dialogue box. Enter this formula in the next panel: =3DAND($A1<=3D$D$1,$A1>=3D$D$1-20) Then click on the Fo...

Plotting a 2 sets of Y data in the X-Y same graph
The data is broken down monthly (X axis) with three or four entries per month counted on the Y axis as bars. Now I have to show the percentage breakdown of that same data on the same graph And your question is...... -- Bernard Liengme www.stfx.ca/people/bliengme remove CAPS in e-mail address "Gonzalo Garreton" <anonymous@discussions.microsoft.com> wrote in message news:0aca01c4a256$79c2e740$a501280a@phx.gbl... > The data is broken down monthly (X axis) with three or > four entries per month counted on the Y axis as bars. Now > I have to show the percentage...

importing data #6
how do i import data from labview to excel sheet Since you didn't say what labview is, I simply googled it. Then I googled labview:excel and got this. http://www.google.com/search?sourceid=navclient&ie=UTF-8&rlz=1T4GFRD_en___US219&q=labview%3aexcel or tiny url http://tinyurl.com/8nmfaz -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com "rajesh" <rajesh@discussions.microsoft.com> wrote in message news:36C05B73-76A0-47FF-86A2-630E5141BCE4@microsoft.com... > how do i import data from labview to excel sheet ...

trouble importing data by paste
I'm using MSP 2007 Std. I'm copying data from a Filemaker (database) screen and updating an MSProject file (with predefined tasks) by pasting it into the project. When the data is pasted, MSP is adding an extra line of data between the last and second to the last lines of data. So, in essence, it is inserting a task. I have no idea where this is coming from. I've experimented and the copied data goes cleanly into an Excel file and cleanly into a blank Project file. Any ideas? or workarounds? I don't have Filemaker to try this; but the way to figure out where...

subform data validation frustration.
I am having frustration with regards to validating data in a sub form of a medical database. Despite lengthy goggling I failed to find an asnwer to date. If I miss out a part dataentry on the subform I receive the message "You cannot add or change a record because a related record is required in table XXX" That is fine but I want to produce a more user friendly error. I had put my validation code int he before update event of teh subform but this does not seem to be triggered. The data in the subform goes to a table called amitanddischarge and does have linked tables to it, which i...

Transposing data
Quick question for something that may or may not be able to be done in excel. I get data in the following format in an excel workbook: a b c d e f 1 yada yada yada yada 2 data1 3 data2 4 yada yada yada yada 5 data1 6 data2 Would like 2 have a second page where it would look like a b c d e f 1 yada yada yada yada data1 data2 2 yada yada yada yada data1 data2 I have tried just doing an =a2 and a3 into e1 and f1 and then autofilling but can never get it done correctly. I thought this was something pretty common so figured I would ask ...

Form allows changes to data but doesn't save or move to next record
The form opens with the first record. You can then scroll through the records or add a new record. However if you change any data the record locks. You can not go to next record and your change is not saved when you close the form. There is no error message. What's going on? -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-forms/200705/1 On Tue, 01 May 2007 21:18:16 GMT, "Eve via AccessMonster.com" <u33563@uwe> wrote: >The form opens with the first record. You can then scroll through the >records or add a new record...

problem selecting rows (or columns)
When working in a large spreadsheet I often have to highlight a range of cells or rows encompassing a few hundred rows When dragging the pointer down to the bottem of the page, the sheet usually moves down in a normal manner. However, at times it speeds up so quickly I can't control where I need to stop. This is NOT the same thing as speeding it up a little by dragging the pointer a little lower, this is FAST. I know others who have this problem I've gone into Control Panel to adjust speed, doesn't work It seems to happen when I've been working on a sheet a long time. Couple ...

How do I strip out some parts of a column of text data?
I have a column of text data, which happens to be 11 characters wide. I want to strip the right-most 6 characters out of the entire column, without having to do each cell one at a time. For example I want to to from this data: 2039 041175 to this data: 2039 for all 350 or so rows of data in the column on the Excel sheet. Use a helper column of formulas =right(a1,6) will return the value as text =--right(a1,6) will return the value as numeric Just copy that down the helper column. footballcmr2 wrote: > > I have a column of text data, which happens to be 11 characters wide. I ...

create list of duplicate data
Hello, I have two columns of data that have duplicates across the columns. I have found many articles on how to remove duplicate data, but how would I create a column that includes only data that is duplicated across the two columns? One simple formulas tinker .. Assume your 2 source data cols are cols A and B, data in row1 down In C1: =IF(COUNTIF(B:B,A1)>0,ROW(),"") In D1: =IF(ROWS($1:1)>COUNT(C:C),"",INDEX(A:A,SMALL(C:C,ROWS($1:1)))) Copy C1:D1 down to the last row of data in col A. Minimize/hide col C. Col D returns the desired results, neatly packed at the top...

Eliminating empty rows in Pivot table
Problem is that you can't simply filter out some data field because in some row fields they have values and at some they don't. So what I essentialy need is for those rows to exist when they contain data and to be erased when not containing data.I hope that it+s possible in excel 2007 Hi, Click on the immediate cell to the right of the Grant Total (for columns) cell, and press Auto Filter. This will get the auto filter drop down on the Grand Total column. Now filter out the Blanks. Hope this helps. -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishma...

Counting Rows #4
I have a sheet in excel, where a user imputs data on one row together with time they were taken. I neet the excel to count the number of inputs every 8 hours and enter the number into another sheet. I had been using VB to operate this. I wanted to convert the time into minutes and then use if to set the range and count the rows. but I dont know how to do this. I will appreciate any idea. Cheers -- Sona ...

Scroll rows with higlighted bar
Hi All, I'd like to have a highligthed bar that I can scroll the rows of a worksheet with the up and down arrow keys, highlinghting one row at a time. NOTE: this is not the same as clicking the row number to highlight the complete row. Is this possible? Thanks D assign to a button or shape sub scrolldown1row() ActiveWindow.SmallScroll Down:=1 end sub -- Don Guillett SalesAid Software Granite Shoals, TX donaldb@281.com "Art" <abpower@aol.com> wrote in message news:3F31312E.3070205@aol.com... > Hi All, > > I'd like to have a highligthed bar that I can scro...

Delete Blank Rows #2
Anyone have a macro or suggestion on how to delete all blank rows in an spreadsheet? When you sort, all the blanks will go to the bottom. Hi Bill, This is a very commonly asked question. You can find several solutions in the Google archives. http://www.google.com/advanced_group_search?q=group:*excel Here is one solution posted on Jan 1/2000. Regards, Kevin ~~~~~~~~~~~~~~~~ This deletes any row that has a blank cell in column A. This should work for you if that condition means the row is blank. Blank, of course, means the cell is empty. With a text file, there could be spaces in the c...

Testing/Finding .Hidden for Rows May21
Hi All, 2 Questions follow, This seems simple, but I'm not seeing it. Below #2 is an excerpt from a bigger function. 1. Is there a quicker way to find rows that are hidden within a larger row range, or is this about the only way? for row = x to y if rows(row).hidden then ..... next row I looked at .Find with the formats it can find but .Hidden is a property, so , no go. 2. I do not understand why the If stmt below evaluated true when all of rows 1 thru 10, inclusive, were not hidden. Function Rows_HiddenQtyFV2(Ws As Workshee...

Combo box change only if other text boxes have data
I have a combo box on a form that has three status options - Open, Closed - NG and Closed - OK. All records in the form are Open but the user can close them only if certain conditions are met. For example, the record status can be Closed - OK only if Countermeasures and a body number are noted (two separate text boxes). A record status can only be Closed - NG if comments (separate text box)are give as to why no countermeasure can be found. How can I prevent the combo box change based on these criteria? On Mon, 3 Dec 2007 19:15:14 -0800 (PST), Opal <tmwelton@rogers.com> wrote: >I ...

Automatic extend row in workshet.
Sir/Mam, I m using worksheet for my Sales entry, which uncertain that how many row it will need for the coming days. Is there any formula. Yes: Based upon your recent historical experience, estimate the number of records generated on a typical day. Multiply that by the number of days for which you are preparing. Finally add a 17% margin. -- Gary's Student "Rao Ratan Singh" wrote: > Sir/Mam, > I m using worksheet for my Sales entry, which uncertain that how many row it > will need for the coming days. > > Is there any formula. ...

Modifying data
Hello, I have a simple spreadsheet with 2000 vendors names in the first column A and address ect data in columns B - K. How can I get the data to reformat to have column A list the vendor name in Cell A1 and all the other info be presnet in B1-10 and then continue for all 2000 vendor names. I've torn my hair out over this....I'm sure it's simple but I haven't had any luck at all... Any thoughts appreciated. Thank You, Extreem Are you trying to make mailing labels? If so you are much better off keeping your data in its current format and using it as a data source in...

Money 2005
I recently purchased Money 2005. I previously used the 2004 version. The main reason I did this is that I got really tired of manually downloading bank statements into Money. I couldn't find a way to automate the process in 2004. I did some serious comparisons with Quicken 2005 and ultimately decided to stick with Money. Here's the problem. In order to automate the download of my bank statements, I find I have to get a passport. Also, I read in a PC Magazine article that this requires you to store your personal information on Microsoft servers. Frankly, I don't trust...

cell formula not updating right after insert row
Hello, I have a formula in a column as follows - the details of what it does are for the most part unimportant (this one is at row 50): =IF(D50<>"",D50-SUM(G50:AA50), IF(C50<>"", E49 +C50-SUM(G50:AA50),"")) I frequently end up inserting rows and copying the above formula to the right column for the new row. What happens though is that this formula and all those following get messed up as so: =IF(D51<>"",D51-SUM(G51:AA51), IF(C51<>"", E49 +C51-SUM(G51:AA51),"")) Everything got adjusted except for...

odbc source data
how to create odbc source data programically? is there any function to do this? winnciki Winnicki wrote: > how to create odbc source data programically? is there any function to do > this? > > winnciki > > Does SQLConfigDataSource do what you want? -- Scott McPhillips [VC++ MVP] i use CRecodset class but first i must prepere regitstered odbc datasource. i know who to do it mannually, but i want to do it also programically. winnicki Uzytkownik "Scott McPhillips [MVP]" <org-dot-mvps-at-scottmcp> napisal w wiadomosci news:OvrKBo15EHA.2704@TK2MSFTN...

Data refresh problem
I downloaded an xl template worksheet which has links to MSN Money website (for updating stock prices) As I have xl97, and the worksheet was created in a later version, I saved it as a xl97 worksheet. When I 'refresh data' it downloads the latest prices.........sometimes. More usually I get the message 'xl has encountered a problem, and needs to close..' Any ideas on what might be causing this? If I knew how the link works, I could start with a new worksheet, but I can't find any web queries relating to this worksheet. Brickcounter Brickcounter, If you know where a...

Implementing a Label with certain data in Chart
Dear All ! I have a data set plotted in Excel. Is there any way to show automatically the minimum, maximum and median value of this data set on the chart? (In a form of label or anything similar) Minimum and maximum values are already part of data set. Median value is a calculated value and is in another cell(not part of data set). Thank You in advance I'd probably do this in a manner similar to what Jon Peltier shows here for the Vertical Category Axis. http://www.peltiertech.com/Excel/Charts/Y_CategoryAxis.html You could then create a custom format for each "Label" s...

Importing Quicksell 2000 DB into RMS: data issues
I just installed RMS and imported the Quicksell DB into RMS. RMS installation and data import procedures were successful. My biggest concern is regarding the way in which the data was imported. In Quicksell 2000, most if not all of my db items were created as matrices. In RMS, the data seems to have been recreated as "standard" items? As a result, I now have multiple occurrences of the same Item with different lookup codes. Im not really concerned about the lookup codes, but more so with the way the data was captured. For example: In QS2000, I had a matrix item of ...

Creating a new table from another
Here is my table for sheet1: Shipment No. Product No. of Pieces A pencil 1 B pen 1 A eraser 1 A paper clip 2 B stamp 3 C ruler 1 B calculator 2 I am trying to create a new table in a new sheet (sheet2) that will list all the products and pieces that were part of shipment no. A This is what I would like it to look like Product No. of Piec...