Formula for formatting rows and columns minimizing the outcome.

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:51:01 PM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
683 Views

Similar Articles

[PageSpeed] 45

You can make use of Advanced Filter option 

1. Assign headers for these columns in row 1.
2. Select Columns B and C
3. From menu Data>Filter>Advanced Filter
4. Select 'Copy to another location'
5. In 'Copy to' select a cell (say G1 )
6. Check 'Unique Records' and click OK..

-- 
Jacob


"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 5:24:01 PM
Reply:

Similar Artilces:

Date format issue when submitting from a userform to a spreadsheet
Hi, I have a userform that I've generated which routes dates onto a spreadsheet based on the users input. I am having a bit of a frustrating time with the dates, it would appear that in the process of moving the date from the userform to the spreadsheet some dates are switched/transposed. I'll give an example. If someone enters 09/02/2004 on the userform excel seems to look at 09 and assume it is a month and transposes the dates to 02/09/2004(this does not appear to be a US/UK format issues as I have already gone down that road). However, if the date 13/09/2004 is entered i...

Please help..with a formula. I don't know code.
I have a long list of numbers - values in a file X, and I want to fin and replace those values in a even larger list in a file Z an highlight those values in Z -- Message posted from http://www.ExcelForum.com Hi not really sure what you're trying to achieve. What do you want to replace, etc. You may give an example (plain text - no attachment please) >-----Original Message----- >I have a long list of numbers - values in a file X, and I want to find >and replace those values in a even larger list in a file Z and >highlight those values in Z. > > >--- >Message...

chart label reference based on the column number
In a worksheet with an embedded chart, I have a cell, S4, where I enter the number of the column I want to chart (these numbers are listed as labels in cells A2:R2). I can't figure out how to translate the number in S4 into the corresponding column letter. For example, if S4 contains "3", the chart title should be =$C$2. I think working with absolute references, R1C1, might be easier here, but somehow what I've tried, didn't work (the entire spreadsheet is based on relative addresses (A1). z.entropic in a cell (Z100) put =index(A2:G2,1,S4,1) Change G2 to whatev...

Custom cell formatting
I need to create a custom format for a series of cells that will begin like this. I can't figure out what the code character is fora volitile potentially alpha character. Can anyone help me?? -Monica, Dallas 000000 000001 000002 .... 000009 00000A 00000B 000010 000011 ...... Monica something like this might work for you but you'll need to put all the leading digits in for the entry with the alpha character Regards Trevor "MDavison" <davison@fr.com> wrote in message news:#SD0tUzTEHA.1652@TK2MSFTNGP09.phx.gbl... > I need to create a custom format for a series ...

Auto formatting features: How do I align page numbers in publicati
I've got a problem with my publication. I can't align even page numbers to the left without automatic moving the odd numbers to the left as well? Can someone help me? Cissy99 wrote: > I've got a problem with my publication. I can't align even page numbers to > the left without automatic moving the odd numbers to the left as well? Can > someone help me? You need to create a two-page master rather than a one-page master. What version of Publisher are you running? -- Ed Bennett - MVP Microsoft Publisher http://ed.mvps.org ...

Need help with formula 01-13-10
I am trying to adapt a formula in I2 from another spreadsheet that works well, but won't in mine. I've traced the error, but I would need help to understand the help it gives! My formula is this: =IF(J2="0-Jan-00","To be advised",WORKDAY(J2,1,NWD)). I have a worksheet in the same workbook with a list of non-workdays, and defined the column of dates with the name "NWD". What I expect the formula to do is this: If J2 is Feb. 4, it would give Feb. 5 in cell I2 because Feb. 5 is NOT a non-workday in NWD. But if J2 is Feb. 5, and Feb. 6 and...

how to turn off automatic format in Excel?
Hi Excel automatically change the first character in a cell to be uppercase. I just want lowercase. How can I turn off this function? Thanks Ngoc Hi Go to Tools / Autocorrect and uncheck Capitalize first letter of sentence. Andy. "ngoc" <linh@chello.no> wrote in message news:BOKNb.271$O41.819@amstwist00... > Hi > Excel automatically change the first character in a cell to be > uppercase. I just want lowercase. How can I turn off this function? > Thanks > Ngoc > ...

Formatting
I've always used MS Word as my email editor in Outlook. Is this possible in Live Mail. Nope. I'm afraid that's only possible using Outlook. In WLM you have to use the built in editor. Is there anything in particular that's lacking from the built in editor that you're looking for? Colin Brown WL MVP "jrchambe" <jrchambe@discussions.microsoft.com> wrote in message news:5EC21892-D39F-4219-AB0F-47BC14E1CD36@microsoft.com... > I've always used MS Word as my email editor in Outlook. Is this possible > in > Live Mail. "...

Formatting Cells in Excel 97
Hi Guys, Sorry if I sound real stupid but is there anyway that we can control the column formatting in Excel 97 like let's say column A = GENERAL(6), column B = GENERAL(4), column C = TEXT(18) etc? Appreciate any form of advice, thanks!! I replied in the programming group. Are you seeking a programming answer? It's usually best to only post to one group, and include the remark "Please tell me if I should ask this in another group." On Thu, 28 Aug 2003 00:56:47 -0700, "Daryl" <daryl.ho@tnt.com> wrote: >Hi Guys, > >Sorry if I sound real stupid b...

Getting Cell Value from the Concatenate formula
I built a concatenate formula that returns the following result: =Jul! $D27 I am looking for the cell contents of Sheet: July Column D Row 27. I tried to use offset, but I am stumped. Can I add something to the front of the concatenate to not only build the reference to the cell, but also return the value instead of the =Jul!$D27 ? Thanks John =indirect(yourformulahere) Don't include the equal sign in your formula. And match the name correctly (Jul or July???). Depending on the name of the worksheet, you may need to have a string that looks like: 'Sheet 99'!d27 =indirec...

stop automatically changing formula!
i have a countif function COUNTIF(Locking!I16:I40,"f") when i copy this and paste it to the next cell, the formula automatically change to COUNTIF(Locking!J16:J40,"f") How do I stop it from changing column I to J?!?!?! thanks. Caryn, =COUNTIF(Locking!$I$16:$I$40,"f") or =COUNTIF(Locking!$I16:$I40,"f") HTH, Bernie MS Excel MVP "caryn" <caryn.tan@gmail.com> wrote in message news:d7n4u3$hgi$1@avnika.corp.mot.com... > i have a countif function > COUNTIF(Locking!I16:I40,"f") > when i copy this and paste it to the nex...

Column comparing
I am trying to compare 2 columns of numbers so that I can identify and delete numbers no longer required. Can anyone help me find a formula for this please? Many thanks DT Check your earlier post. Dave T wrote: > > I am trying to compare 2 columns of numbers so that I can identify and > delete numbers no longer required. Can anyone help me find a formula for > this please? > > Many thanks > DT -- Dave Peterson ...

Hyper link one column to another
I would like to have my columns hyper link one another. For example: Click on B2 would take you to N2, and vice-versa. Click on C2 would take you to O2... and so on through column j linked to V. Is this possible without having to make the link for each cell? This is a timesheet template and the columns b though J are the hours and N through V are the text comments for those hours. When I copy the template to a new sheet, there will be no data in any column. I would like to be able to enter an amount or formula (=end-start) for time spent and then be able to click on that cell and hyperlin...

format a CD
Hi, how can I format a cd+rw re writeable disc aga ...

combining columns all the way down
I am trying to combine two columns of information in excel but th concatenate function doesnt seem to work for it. It may just be m ineptitude in excel but I just cant seem to figure this out. I have 3 colums Column A__________Column B___________Column C tree_______________ .jpg dog________________.gif House______________.png But I cant seem to be able to make it so that column C has tree.jpg an dog.gif and house.png Is concatenate the wrong thing to use here? or am I making my formula incorrectly -- sparkrom ----------------------------------------------------------------------- sparkro...

Named ranges
Excel 2003 I have a total sheet that gets data from three different shift sheets (all in the same workbook) for a monthly report. I have named ranges on each shift sheet for each month's production data (S1M1, S1M2, S1M3, S2M1...) where S1M1 =A2:AC147, S1M2 = A148:AC329, etc. The old formula was:=SUMIF('SHIFT 1'!$L$1:$L$147,"BB",'SHIFT 1'!$T$1:$T$147). I'm stumped as to what the new formula would be - how would I direct Excel to column L of S1M1 to search for "BB", then add the contents of column T? The range changes each month, and I tho...

PivotTable Formulas
I have set up a pivot table. I have been asked to add a column at the end of the pivot table that calculated the %variance of sales from this year versus last year. I tried to create the formula (Calculate Field) after I had grouped my dates for Months & Years. I had to ungroup these to be able to create the formula. I do not see how when I have ungrouped the date to distinguish between 2003 & 2002 in my Pivot formula. Is this possible of am I flogging a dead horse here? Any suggestions would be most help ful Regards GarethG ------------------------------------------------ ~~...

Look up column name, match rows (a/cnumber) & summing up....its confusing!
Hi All, Here is what I'v been trying to do but..! I'v 2 sets of data (data1 & data2) Column name may be sane/different in each data set and same applies to acct_no.. I want to prepare a report that combine product & accounts data show accumulatd result on another sheet/file. Data 1 - upto Jan 2006 Acct. No Total Prod - A Prod - B Prod-C 1001 51 10 30 11 1002 47 15 20 12 1003 80 20 15 45 1004 64 25 16 23 Total 242 70 81 91 Data 2 - Feb 2006 Acct. No Total Prod - A Prod - B 1002 7 5 2 1004 16 10 6 1009 9 3 6 Total 32 18 14 Report required Acct upto Jan'06 Prod - ...

copying rows from next sheet over
I'm trying to copy a range of rows from one sheet over from the activ sheet, and paste them onto another sheet. This is what I tried: ActiveSheet.Next.Range("a2:b100").select selection.copy However, I'm getting the error "selection method of the range clas failed" What am I doing wrong, and is there an alternative way to do this? Any help would be appreciated -- ayl32 ----------------------------------------------------------------------- ayl322's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=984 View this thread: http://www.excel...

Need to have more Columns available in advanced view
I know how to add columns in advanced view but i can't add all the columns I'd like to add. I can choose more fields (attributs) as search attributes than as result columns. Is there somewhere a switch to turn a field (attribute) into not only beeing searchable but selectable as a column in advanced search? Example: "Invoice Product": Is there a way to make an advanced search or view which delivers field (attributes) of "Invoice Product" as a result? Marko ...

Remove format link?
Is there a way to remove the format link between fields in Modifier? Help just shows how to set it and the usual stuff doesn't seem to be working. ...

Formula #50
I currently am working on a few spreadsheets containing daily sale readings from 8 different stores. I have created a chart to show the days sales comparing it to last years sales (same day but for 2004) and would like to create either a formula and/or link to the second sheet so that it automatifally imports the info as opposed to me changing the formula every week....now this is done for all days of the week. Please HELP! -- precioustj Can you give an example of the data you have. Are you trying to match two different pieces of data or just one? Thanks. "precioustj" w...

setting a column value in code for an updatable ViewGrid
Is there a way to set the value for a column in code when a ViewGrid row is edited? I have a row-last-updated date that is a required audit field in all our systems (required by data management). I don't really want the user updating this field as it should always be system controlled. If I create an editable ViewGrid tied to either an SqlDataSource or an ObjectDataSource, the field seems to only be updatable by the user. I can set it the first time by setting the DefaultValue in code but that has no effect after there is an initial value in the column. Do I need to co...

Word doc format
I tried to email a word attachment written on office for mac 2008 home version, but the recipient couldn't open the doc. recipient using windows xp. rlciii@officeformac.com <rlciii@officeformac.com> wrote: > I tried to email a word attachment written on office for mac 2008 home > version, but the recipient couldn't open the doc. recipient using windows > xp. If they don't have a recent version of Windows Office, they may not be able to open a .docx file. Choose File > Save As and save as a .doc file. -- Adam Bailey | Chicago, Illinois adamb@lull.org | Finge...

Currency Format Lost in Union Query
Thanks to John Spencer, I resolved one problem in my union query (Thank you, John). But I have another problem. Some of my fields in my union query are currencies and others percents. I see both formats are lost. I am sure it is a union query problem, because when I ran an individual query, none of the formats were lost. Is there any way I can keep currency and percent formats without writing, FORMATCURRENCY, etc for each field? Thank you. What data types are these fields? The field in a UNION query will normally take on the data type of the field in the first SELECT. So, if ...