EXCEL anyone?

[camilito]

well this is quite challenging...
ok heres the deal...

lets say we have a sample in an excel file consisting of : description, part number and quantity like this one :

Description..................Part number...........Quantity
PART1234-1/4"............12345......................1
NUT12-1/8"-VLC..........ch12-23....................1
NUT12-1/8"-VLC..........ch12-23....................1
PART4321-1/4"...........09876........................1
NUT12-1/8"-VLC..........ch12-23a .................1
WELD 14-DCD.............47564......................1
STUFF 12-23-123........sdfgsdf234.................1
PART4321-1/4"...........09876.......................1
WELD 14-DCD............13421........................1
STUFF 12-23 A...........sadfsdf456..................1
PART4321-1/4"...........09876........................1

What I need help on is figuring out how to identify which part descriptions repeat in the list (like NUT12-1/8"-VLC) and then compare the part numbers of any repeated description to see if they are exactly the same (there's three NUT12-1/8"-VLC's but only 2 of them have the same Part number)
After making sure the parts are exactly the same I need to make the quantity value of that part change according to how many identical parts were found (in this case make the Quantity of NUT12-1/8"-VLC with part number ch12-23 show there are 2)
Finally any identically repeated parts should be deleted from existence to never be seen again"¦

the Description should also be change to lowercase in order to be more accurate if this example were to be applied to a larger sample size ...( maybe 7000+ entries)


Anyone up to this challenge?

PS: sorry for not providing the actual excel sample .. cant upload..damm upload quota

[NikG]

Sow what exactly are you looking for? Formulas? A macro?

It's not at all hard to to do this. Just create keyfields for each row, and then use the COUNTIF formula. A keyfield is something that you use to give an identity to each row, thus allowing you to distinguish between rows.
- To create the keyfield, create a column after your Part Number column. Type in the fomula "=A1&B1" in C1 and drag it down. (I also suggest at this point you convert that column to values and sort the entire list by your keyfield column.)
- Once you've done this, just add this formula in the next column: "=COUNTIF($C$1:$C$11,C1)" where "$C$1:$C$11" represents all the keyfields you just created and "C1" represents the keyfield in the row you are in (the value you are searching for). Drag this formula all the way down as well.

What you'll have is a number beside each keyfield representing how many times it exists in the list and, if you sorted them like I suggested, they will be next to each other. Then it's a simple matter of deleting the rows with the duplicate #s. (If you want this to be done automatically, the only way is to create a macro.)

Let me know if you need clarification and/or further help.

[camilito]

im pretty much looking for formulas....
maybe defining some names for the collums ... and write one really good formula that juts needs to be draged down...
now... if i use COUNTIF and define names for each colum ...will it still work?... or do i just fill in like u said (=countif($c$1:$c$11, $c$1))

thing is i have an extremely large file in excel .. and i dont wana delete stuff by hand... it will eat my mind away...


a macro...
now thats interesting...





so whats a macro again? ... just so that other people know...

[NikG]

camilito wrote:

im pretty much looking for formulas....
maybe defining some names for the collums ... and write one really good formula that juts needs to be draged down...
now... if i use COUNTIF and define names for each colum ...will it still work?... or do i just fill in like u said (=countif($c$1:$c$11, $c$1))

thing is i have an extremely large file in excel .. and i dont wana delete stuff by hand... it will eat my mind away...

I'm not sure what that you meant by that first paragraph. You need to create a keyfield column because the countif need something static to search through.

camilito wrote:

a macro...
now thats interesting...

so whats a macro again? ... just so that other people know...

Macros are scripts written in Visual Basic for Microsoft Office applications (except MS Access, which uses SQL I believe). They are used to automate tasks and are very useful. (In fact, I'd bet my macro-programming knowledge is the reason I've netted some of my co-op jobs)

Back to the problem at hand, you could create a simple macro which will do the deleting for you, or you could create a slightly more complicated macro that will do everything for you (including creating the keyfield column, using the countif, deleting duplicate rows, and if so desired, deleted the keyfield column afterwards).
Thing is, I wouldn't recommend bothering to code this macro if this is a one-time task. (Alternatively, if you need to do this task repeatedly, then you SHOULD create a macro).

Anyways, if you know some basic Visual Basic and you're interested in making a macro, I'll be glad to help.

[camilito]

ok i uploaded a sample .... i did the keyfield thing... (hopefully sorted it right) ... but now i dont understand the results....

it would be great if u could help me on a macro... i do have some visual basic knowledge ...but not as much as i would like to have..

i would like to correct whatever hapened in the sample i attached first...

we'll keep the macro simple... just delete the extra rows for now...
(im gueesing if i do the countif in excel first i wont have to do it in the macro..right?)

[NikG]

You just have one little mistake.
Your countif seems to be pointing at the previous row (i.e. you have "=COUNTIF(Keyfield,C1)" in row 2... shouldn't that be C2?)
Just change them all to one number ahead.

I'll try and write some code for the macro.

[camilito]

i was looking around and found this:

http://office.microsoft.com/en-us/assistance/HA010346261033.aspx

didnt know i could "filter" the repeated rows...
I did a keyfield colum .... and the followed those steps ...

im still interested in that macro though....

[camilito]

ok i posted a better sample....
A veeeery small sample compared to the whole thing....

I put some comments on each heading... if u have questions... post em

im amazed only one person feels comfortable enough with excel to post replies... isnt excel something widely used?

props to you NikG for the help..

[NikG]

Sorry for not responding earlier bud, I haven't had a lot of free time.

Ok, I took a look at your Sample2.xls file and... couldn't understand what was going on. haha.

Couple of things:
-I noticed you mentioned that to change the rows from formulas to raw text, you were copying them onto Word and them pasting them back.
You don't need to go through all that trouble.
Just select the column with the formulas, right click on it, copy, right click on it again, paste special, choose the "values" radio button and click ok.

-I forgot about filtering, it's actually a better option than a macro. I don't know how you did it, but here's what to do step by step.
-Create the keyfield column
-Change that keyfield column into values (as described above)
-Select all data (original + keyfield)
-Advanced filter it to show unique records only.
-Copy the results (into a temporary sheet if you want)
-Show all data (Data>Filter>Show All) and delete it all
-Paste back the filtered data you just copied.

[camilito]

lol sorry u didnt understand my second sample ...

supposing I folowed ur instructions...

the result is.... how many times the keyfield appaears... and then the deletion of any extra rows... so it shows only unique rows....

that helps a little ..but what i must do is add the quantities of the repeated rows.... before they are deleted...


this is an example of what we just did

keyfield = NUT1/2"H23K42TRUMP
Number of time it repeats = 2

in the original data
keyfield = NUT1/2"H23K42TRUMP
Quantity = 4
keyfield = NUT1/2"H23K42TRUMP
Quantity =2

....what this means is that COUNTIF counted the NUT 2 times "¦.one with quantitty 4 and other with quantity 2 (or any other quantity..might be 5 or 6 or 50).. Since this is the case what i need is to get an overal quantity ...in this case 6 .... and THEN delete the rows...

[camilito]

i was also looking to use Subtotals...

but i would still need a macro to delete any Blank cells and any unwanted data ( like headers inside a colum)

[rizzix]

Hey! Btw your macro is something along the lines:

code:

For Each cell in Sheet1.Range("A1:A20")     If Trim(cell.Text) = "" then         cell.Delete     End If Next

Where Sheet1 is the name of the sheet in question and "A1:A20" is the column range.

[NikG]

camilito wrote:

....what this means is that COUNTIF counted the NUT 2 times "¦.one with quantitty 4 and other with quantity 2 (or any other quantity..might be 5 or 6 or 50).. Since this is the case what i need is to get an overal quantity ...in this case 6 .... and THEN delete the rows...

It should not be doing that.
If the 2 are exactly identical and your formula is picking up all the data, then it should count 6. Make sure the keyfields are exactly the same. 2 things to check for:
-Extra spacing in the keyfields... that could be the reason it picks it up twice.
-Your countif formula. Make sure it's not shifting down. (i.e. make sure it looks something like =countif($A$1:$A$10,B1)... the dollar signs are important if you dragged the formula down).