How to delete those rows which has same positive & negative value

**joyharry** · 05-21-2013, 03:26 AM

Hi All,

Forgive me if i am not in the correct forum.

I am new in excel. Need urgent help in excel, as i am stuck with something for so long.

My problem :
" I am taking huge data file. and i need to take two columns in consideration ColumnA(name) and ColumnB(hours). ColumnB presents the hours spend by users on activities, Sometime it is like this :
Anish -> +40
Anish -> -40
Sneha -> +50
Sneha -> -50
Raghu -> 45
Pradip -> 23

what i need to do it is.. delete those rows which has same user's name and same hours(+40, -40) as the sum is zero, so we dont need these names to show in report.
for example - I need to delete Row1,2,3 &4 in above given sample.
What I am expecting is, Is there any way I can highlight these rows then rest of the things will be easier to me.

Kindly provide your earliest help.
Thanks
JoyHarry

**dilipandey** · 05-21-2013, 03:33 AM

Hi JoyHarry,

welcome to the forum.
Please change the thread title to reflect the query you are asking :-

http://www.excelforum.com/forum-rule...rum-rules.html

Regards,
DILIPandey
<click on below * if this helps>

**joyharry** · 05-21-2013, 04:12 AM

@dili

thanks for directing me to this link.

**dilipandey** · 05-21-2013, 07:51 AM

Hi Joyharry,

see the attached file where I used below formula to obtain desired results:-

Formula:

Please Login or Register  to view this content.

Now filter column C for 1 and that is your required data

del dup sets.xlsx

Regards,
DILIPandey
<click on below * if this helps>

**joyharry** · 05-22-2013, 04:06 AM

Hi
Thanks 4 ur solution.

This solution is working, but it is not working correctly for huge data. When i apply this formula, I got multiple of numbers 0,1,2,3,4,10,12 like this.

Can you tell what could be the reason?

**dilipandey** · 05-22-2013, 04:29 AM

Now filter column C for 1 and that is your required data

Is that not solving your problem ?
If yes, then upload your data .. thanks.

Regards,
DILIPandey
<click on below * if this helps>

**Abdul Basit** · 05-22-2013, 06:34 AM

Dear Friend,

1st go next column and take formula =abs(b column) pull down the formula until your data end.

Then select the whole your data with extra row of Abs and move your scroll up and click data find the remove duplicate and select A & C Column.

Take Care

Regards,

Abdul Basit

**joyharry** · 05-23-2013, 12:52 AM

yes, its not working.

Actually I want to delete those rows where the sum is zero because of same positive & negative numbers. and in my data file, There are many duplicate names in column A and dats y it is not giving expected result.

test.xlsx

**Abdul Basit** · 05-23-2013, 01:29 AM

Dear Bro,

The simple formula abs used now check the screenshot.

**joyharry** · 05-23-2013, 02:15 AM

Abdul,

I think you misunderstood my query.I want to delete those rows which has same name in columnA & same positive & negative numbers in ColumnB.

I want this to happen because the sum of hours will be zero for these names.

**jolivanes** · 05-23-2013, 02:38 AM

If you would want to try code, you could try this on a copy of your workbook.
Names are in Column A (starting at A1) and hours are in column B

Please Login or Register  to view this content.

**Abdul Basit** · 05-23-2013, 03:01 AM

Dear Bro ,

Check the file the fourth column "check it" . and filter the numbers except zero..

May be this file can help you..

**jindon** · 05-23-2013, 03:13 AM

Try the attached

**joyharry** · 05-23-2013, 04:11 AM

@Jindon,

This is working. Can you plz explain the code as I am new to VBA. Little explanation will be very helpful.

thanks very much

**jindon** · 05-23-2013, 04:23 AM

Actually the code should be like this in case of multiple exact same amount....

Please Login or Register  to view this content.

1) Main dictionary store its child dictionary for each emppyee.
2) each child dictionary stores absolute amount of hours as its key and set the reference to that cell for its item property.
3) when duplicates find and the balance is 0 then set variable x ( as range) to those cells.
4) delete x when x is not nothing.

Helps?

**Marcol** · 05-23-2013, 05:07 AM

Maybe this formula in C2, Drag/Fill Down.

Formula:

Please Login or Register  to view this content.

Filter C2 for "Delete", then delete the resulting rows.
Remove the filter and clear Column C

**kalak** · 05-23-2013, 06:46 AM

Alternative code. Its workings should be reasonably intuitive.
Deletes matching + and - for first two columns only, but easily modified to do entire row if that's wanted.

Please Login or Register  to view this content.

**joyharry** · 05-24-2013, 01:16 AM

Hi guys,

these codes are working fine. but when i am trying to run these on codes on my original file it is giving me some wrong picture. I think I am making some mistake.
I am attaching my original file. Please attach code to that file.

check.XLS

I will really appreciate your kind efforts.

thanks
Joy!

**jindon** · 05-24-2013, 01:23 AM

Replace All r(, 2) With r(, 10) in my code

**joyharry** · 05-24-2013, 01:45 AM

I replaced r(, 2) with r(, 10) but still it was giving me wrong picture. but now it worked!!!

I was making a small and silly mistake.

Thank you guys & community for your earliest help. I was new in this community but the way you all provided your help, I truly appreciate that

!!

Thank you very much!!
Joy

**joyharry** · 05-24-2013, 03:07 AM

Hi,

I have 1 more question regarding this code.
What if i want to remove these rows with two criteria? Like, earlier I wanted those rows to be deleted which has same empl name & same positive & negative numbers(hours).

And now if I want those rows to be deleted which has same name, same positive & negative number(hours) and those hours are of same month. for example you can see attached file.

check.XLS

**jindon** · 05-24-2013, 03:24 AM

You have 2 date columns and I don't know which one you are talking about, so I will leave this to the others.

**joyharry** · 05-24-2013, 03:35 AM

I am talking about Column I(empl name), J(hours) & O(Claim_month)

How to delete those rows which has same positive & negative value

LinkBack

Thread Tools

Rate This Thread

Display

How to delete those rows which has same positive & negative value

Re: Need Urgent Help!!

Re: Need Urgent Help!!

Re: How to delete those rows which has same positive & negative value

Re: How to delete those rows which has same positive & negative value

Re: How to delete those rows which has same positive & negative value

Re: How to delete those rows which has same positive & negative value

Re: How to delete those rows which has same positive & negative value

Re: How to delete those rows which has same positive & negative value

Re: How to delete those rows which has same positive & negative value

Re: How to delete those rows which has same positive & negative value

Re: How to delete those rows which has same positive & negative value

Re: How to delete those rows which has same positive & negative value

Re: How to delete those rows which has same positive & negative value

Re: How to delete those rows which has same positive & negative value

Re: How to delete those rows which has same positive & negative value

Re: How to delete those rows which has same positive & negative value

Re: How to delete those rows which has same positive & negative value

Re: How to delete those rows which has same positive & negative value

Re: How to delete those rows which has same positive & negative value

Re: How to delete those rows which has same positive & negative value

Re: How to delete those rows which has same positive & negative value

Re: How to delete those rows which has same positive & negative value

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions