lists and more lists

[mrxwantobe]

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

[s-o-s]

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.

[Aladin Akyurek]

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

[mrxwantobe]

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"