Totaling up the duplicated info - delete the excess - delete the Rev from the description

**legeorgia** · 07-24-2012, 04:21 PM

Hello Everyone,

Need your superior excel skills. I have a spreadsheet that I need to do the following -

Total how many duplicated part numbers there are.
Deleting the duplicates
Remove the Rev info at the end.

I hope I explained it correctly.

For example:

I need it to go from this:

179049-101 C 15 FIXED OVERHEAD Rev C
179049-101 C 15 FIXED OVERHEAD Rev C
179049-101 C 15 FIXED OVERHEAD Rev C
179049-101 C 15 FIXED OVERHEAD Rev C
179049-101 C 15 FIXED OVERHEAD Rev C
179049-101 C 15 FIXED OVERHEAD Rev C
179458-101 A 15 RETRACT REV A
179458-101 A 15 RETRACT REV A
179458-101 A 15 RETRACT REV A
179458-101 A 15 RETRACT REV A
179458-101 A 15 RETRACT REV A
179458-101 A 15 RETRACT REV A
179458-101 A 15 RETRACT REV A
179458-101 A 15 RETRACT REV A
179469-001 B CA ASSY SEB-DISC, DBL AUDIO Rev B
179469-001 B CA ASSY SEB-DISC, DBL AUDIO Rev B
179469-001 B CA ASSY SEB-DISC, DBL AUDIO Rev B
179469-001 B CA ASSY SEB-DISC, DBL AUDIO Rev B
179469-001 B CA ASSY SEB-DISC, DBL AUDIO Rev B
179469-001 B CA ASSY SEB-DISC, DBL AUDIO Rev B
179469-001 B CA ASSY SEB-DISC, DBL AUDIO Rev B
179469-001 B CA ASSY SEB-DISC, DBL AUDIO Rev B
179469-001 B CA ASSY SEB-DISC, DBL AUDIO Rev B
179469-001 B CA ASSY SEB-DISC, DBL AUDIO Rev B
179469-001 B CA ASSY SEB-DISC, DBL AUDIO Rev B

To this:

179049-101 C 15 FIXED OVERHEAD 6
179458-101 A 15 RETRACT 8
179469-001 B CA ASSY SEB-DISC, DBL AUDIO 11

Thank you in advance.

**oeldere** · 07-24-2012, 04:41 PM

The first part can be done by:

Excel 2007 => data => remove duplicates.

For the second question i will look for an anwer.

---------- Post added at 03:41 PM ---------- Previous post was at 03:28 PM ----------

This recorded code will do that for you

I tested it and it worked.

If you don't want to use a macro you can use for the second question.

CTRL + H

Find REV *
Replace (leave blanc)
Replace all.

Do you have questions, on this item, just ask.

Please Login or Register  to view this content.

**legeorgia** · 07-24-2012, 05:26 PM

When I tried to do this it did remove the revs, compiled them to show only one of each part number, however it did not calculate.

---------- Post added at 02:26 PM ---------- Previous post was at 02:25 PM ----------

Thanks for the quick tips, That will difinetly come in handy. But I need to count them before I delete them.

**oeldere** · 07-24-2012, 05:28 PM

You get your wanted result, as you asked in #1?

Please Login or Register  to view this content.

What should be calculated?

OK, I see, I see what i can do for you.

**legeorgia** · 07-24-2012, 05:31 PM

Ok, after taking another look I see what you did. You deleted the dups in column A and removed the Revs in column B. But A and B are tide. So if A's duplicates are deleted, then so should B. I need to count them then delete the dups as my example.

Thanks for the effort. It was appreciated - you gave me some ideas, but this is not what I'm looking for.

**oeldere** · 07-24-2012, 05:36 PM

I don't know exactly what you want to count.

So I added 2 methods in the example.

The green cell should be the answer (3).

**oeldere** · 07-24-2012, 05:39 PM

Please Login or Register  to view this content.

I think it's only value in column A.

See the example in #6.

**legeorgia** · 07-24-2012, 05:48 PM

The first count is what I need.

However:
Column A should only have the part number
Column B should only have the description

So I need to go from this:

Column A Cloumn B
179049-101 C 15 FIXED OVERHEAD Rev C
179049-101 C 15 FIXED OVERHEAD Rev C
179049-101 C 15 FIXED OVERHEAD Rev C
179049-101 C 15 FIXED OVERHEAD Rev C
179049-101 C 15 FIXED OVERHEAD Rev C
179049-101 C 15 FIXED OVERHEAD Rev C
179458-101 A 15 RETRACT REV A
179458-101 A 15 RETRACT REV A
179458-101 A 15 RETRACT REV A
179458-101 A 15 RETRACT REV A
179458-101 A 15 RETRACT REV A
179458-101 A 15 RETRACT REV A
179458-101 A 15 RETRACT REV A
179458-101 A 15 RETRACT REV A
179469-001 B CA ASSY SEB-DISC, DBL AUDIO Rev B
179469-001 B CA ASSY SEB-DISC, DBL AUDIO Rev B
179469-001 B CA ASSY SEB-DISC, DBL AUDIO Rev B
179469-001 B CA ASSY SEB-DISC, DBL AUDIO Rev B
179469-001 B CA ASSY SEB-DISC, DBL AUDIO Rev B
179469-001 B CA ASSY SEB-DISC, DBL AUDIO Rev B
179469-001 B CA ASSY SEB-DISC, DBL AUDIO Rev B
179469-001 B CA ASSY SEB-DISC, DBL AUDIO Rev B
179469-001 B CA ASSY SEB-DISC, DBL AUDIO Rev B
179469-001 B CA ASSY SEB-DISC, DBL AUDIO Rev B
179469-001 B CA ASSY SEB-DISC, DBL AUDIO Rev B

To this:
Column A Column B Column C
179049-101 C 15 FIXED OVERHEAD 6
179458-101 A 15 RETRACT 8
179469-001 B CA ASSY SEB-DISC, DBL AUDIO 11

---------- Post added at 02:48 PM ---------- Previous post was at 02:47 PM ----------

Column C totals all the info into one row for each dup.

**oeldere** · 07-24-2012, 05:58 PM

Please reply in my example how the data are in the beginning (how many column) and with which data in it.

Please also, reply in a column (manualy) what the result should be.

**legeorgia** · 07-24-2012, 06:21 PM

I did above, in the first and 8th thread.

There should be two columns at first.
Column A - has the part numbers
Column B - has the description

Column C - will have the total for how many times each part#/Description appeared.

All the duplicates will then be deleted and the final result should look like this (I use "......." to display column spacing):

Column A ................. Column B............................. Column C

179049-101 ..............C 15 FIXED OVERHEAD ...............6
179458-101 A........... 15 RETRACT .............................8
179469-001 B ...........CA ASSY SEB-DISC, DBL AUDIO ...11

I'm sorry if I'm confusing you, this is the only way I know how to explain it.

Thanks again.

**oeldere** · 07-24-2012, 06:37 PM

Why don't you put the value in my excel-example.

Then it's easier to see how the data is build up.

Partnr. means nothing to me
Description means nothing to me

179049-101 is without a letter.

179458-101 A is with a letter.

Both are right?

**legeorgia** · 07-24-2012, 06:53 PM

That's ok, I'll try to work it out. Thank you for your help.

**oeldere** · 07-24-2012, 07:10 PM

Probably it is my problem, but i don't like it, you post the question again on this forum.

http://www.excelforum.com/excel-form...t-the-end.html

**legeorgia** · 07-24-2012, 07:59 PM

You could not help me so you blocked me from getting help and sent me a private message. Please just leave it to someone who can help me.

I can't help you being mad. I was not helped.

**icestationzbra** · 07-24-2012, 09:50 PM

legeorgia, see if this helps meet your requirement. there is a helper column that i had to use, other than that, everything else is pretty much what you had illustrated.

EDIT: uploaded a new file which solves the problem without any helper column.

by the way, if you do not provide a sample file with your data, and just provide a lot of data pasted into the post, you may experience that people shy away from responding because they have to recreate data. i would have, too, until i saw your predicament.

**legeorgia** · 07-24-2012, 10:54 PM

Thank you. I'll try it once I get in the office. I'm not experienced at doing this. If it's attaching a file I will for sure try it next time.

Thanks so much for your help. I'll let you know tomorrow if it worked.

**oeldere** · 07-25-2012, 02:03 AM

@icestationzbra

For your explanation, i will and can help the OP.

But if he / she refuses to add an excel example it's difficult to get the right solution.

I don't think you give the desired result (but of course i let that decision, to the OP)

The (input)data are in several columns, but it's not clear to me, which data are in which column.

@Legeorgia

In #9 en #11 i ask you to post an excel example.

In #10 you post you data on the forum again.

I will explain why I asked you for an example.

I gave you an solution if the (imput)data are in 1 column.

In #8 you changed the question => you added the information that the (imput)cells are on several columns.

2 things on this.

If you don't want any help from me, just say so, then it is clear for both of us.

It will show some respect to eachother to do so.

Please Login or Register  to view this content.

You acted against the forumrules.

In your own environment (i suppose) you also want people to obey to the rules.

**legeorgia** · 07-25-2012, 11:33 AM

Yes, this is it exactly. Thank you so very much icestationzbra. You helped me out a great deal; and thank you for letting me know what to do going forward.

Have a great day!!!

Totaling up the duplicated info - delete the excess - delete the Rev from the description

LinkBack

Thread Tools

Rate This Thread

Display

Totaling up the duplicated info - delete the excess - delete the Rev from the description

Re: Totaling up the duplicated info - delete the excess - delete the Rev from the descript

Re: Totaling up the duplicated info - delete the excess - delete the Rev from the descript

Re: Totaling up the duplicated info - delete the excess - delete the Rev from the descript

Re: Totaling up the duplicated info - delete the excess - delete the Rev from the descript

Re: Totaling up the duplicated info - delete the excess - delete the Rev from the descript

Re: Totaling up the duplicated info - delete the excess - delete the Rev from the descript

Re: Totaling up the duplicated info - delete the excess - delete the Rev from the descript

Re: Totaling up the duplicated info - delete the excess - delete the Rev from the descript

Re: Totaling up the duplicated info - delete the excess - delete the Rev from the descript

Re: Totaling up the duplicated info - delete the excess - delete the Rev from the descript

Re: Totaling up the duplicated info - delete the excess - delete the Rev from the descript

Re: Totaling up the duplicated info - delete the excess - delete the Rev from the descript

Re: Totaling up the duplicated info - delete the excess - delete the Rev from the descript

Re: Totaling up the duplicated info - delete the excess - delete the Rev from the descript

Re: Totaling up the duplicated info - delete the excess - delete the Rev from the descript

Re: Totaling up the duplicated info - delete the excess - delete the Rev from the descript

Re: Totaling up the duplicated info - delete the excess - delete the Rev from the descript

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions