lists and more lists

mrxwantobe

Board Regular
Joined
May 2, 2002
Messages
158
ok folks is this possible?
column A is a cust number, column B is a name and column C is a dollar amount. I need to find the duplicate cust number from A and move the dollar amount to D next to the existing number in C and possibly delete the A, B, and C from the duplicate.

Thanks
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
On 2002-05-03 15:21, mrxwantobe wrote:
ok folks is this possible?
column A is a cust number, column B is a name and column C is a dollar amount. I need to find the duplicate cust number from A and move the dollar amount to D next to the existing number in C and possibly delete the A, B, and C from the duplicate.

Thanks

If you have one match this will find a match and put the value in D of the other cell.

=OFFSET(C4,MATCH(A4,A5:A$65536,0),0)

My guess is that this is not the end of the problem, so here are some questions:

Will the be multiple duplicates?
Are you adding items to the list as you are going on (This will make life a little easier)

There's another question in there that I can't get to at the moment just waiting to pop out.

Once we've got that then you can look at the macro code to delete data etc. Which whould be the next bit.
 
Upvote 0
On 2002-05-03 15:21, mrxwantobe wrote:
ok folks is this possible?
column A is a cust number, column B is a name and column C is a dollar amount. I need to find the duplicate cust number from A and move the dollar amount to D next to the existing number in C and possibly delete the A, B, and C from the duplicate.

Thanks

Lets say that A1:C6 houses the following sample:

{"c#","cname","amount";
1,"a",20;
1,"a",10;
2,"b",6;
3,"c",7;
2,"b",12}

This is important: Insert a new column after column B.

In (new) C1 enter: Amount

In C2 enter and copy down:

=SUMIF($A$2:$A$6,A2,$D$2:$D$6)

Now, the data area will look like this:

{"c#","cname","amount","amount";
1,"a",30,20;
1,"a",30,10;
2,"b",18,6;
3,"c",7,7;
2,"b",18,12}

This is important: Format the labels in row 1 very distinctly, e.g., in bold and italic.

Now activate A2.
Activate Data|Filter|Advanced Filter.
Check Copy to another location.
Enter $A$1:$C$6 for List range.
Enter $H$1 for Copy to.
Check Unique records only
Leave everything else alone.
Activate OK.

You might want to delete columns A to D.

Aladin
 
Upvote 0
it is a monthly sales report. i need to take next months report and move the sales figures from "C" up to "D" with the corosponding cust number. i thought i could paste the new report in the existing one and by finding the duplicates move the new months figures up to "D"
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,949
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top