Hello - Please I need help to create macro to calculate sum of Revn column V by PO column F and copy single PO with sum greater than or equal 40K to new sheet.
Macro to filter data
- MED AMINE
- Thread is marked as Resolved.
-
-
-
A macro is not required for this. A PivotTable can be used with a Value filter of "Greater Than Or Equal To" 40000
-
The question is not clear.
Below question has many doubts. F is not pure numeric column hence greater than function cant be applicable.
And how come V column calculates by F column ? V is Numeric and F is text cum numeric based.
calculate sum of Revn column V by PO column F and copy single PO with sum greater than or equal 40K to new sheet.
-
Thanks for your reply.
I am looking to copy data sheet and macro filter data for me. F column is not numeric but if you look at row 3 and 4 is the same PO, so total revenue for this PO is 8552 ... I want macro to check PO is the same calculate revenue and highlight if more than or equal 40K. -
if my understanding is correct from your recent post.
you want to check duplicate POs first in column F, and those duplicate POs against Revenue cells in column V to be calculated with addition formula, and finally highlight those are equal or more than 40 k.
----------
but in the data sheet that you attached having format problems. in column F, row 3 & 4 cells highlighted in red color below having spaces in between numbers which cant be recognised by the computer. pls clear those format related issues.
[tr]
[TABLE="border: 0, cellpadding: 0, cellspacing: 0, width: 939"]
[td]Ord
[/td]
[td]Nbr
[/td]
[td]Account
[/td]
[td]Bilt
[/td]
[td]Bilt Name
[/td]
[td]Po
[/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td]Revn
[/td]
[td][/td]
[/tr]
[tr]
[td]123455
[/td]
[td]23456
[/td]
[td]34656
[/td]
[td]2568069
[/td]
[td]Cust X
[/td]
[td]FR256806
[/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td]3555.615
[/td]
[/tr]
[TD="align: center"]#VALUE![/TD]
[tr]
[td]123456
[/td]
[td]23456
[/td]
[td]34656
[/td]
[td]3115133
[/td]
[td]Cust X
[/td]
[td]2018 03 07
[/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td]3566
[/td]
[/tr]
[TD="align: center"]#VALUE![/TD]
[tr]
[td]123457
[/td]
[td]23456
[/td]
[td]34656
[/td]
[td]3115133
[/td]
[td]Cust X
[/td]
[td]2018 03 07
[/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td]4986
[/td]
[/tr]
[TD="align: center"]#VALUE![/TD]
[tr]
[td]123458
[/td]
[td]23456
[/td]
[td]34656
[/td]
[td]5175186
[/td]
[td]Cust X
[/td]
[td]NVBY98S
[/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td]1125.668
[/td]
[/tr]
[TD="align: center"]#VALUE![/TD]
[tr]
[td]123459
[/td]
[td]23456
[/td]
[td]34656
[/td]
[td]3590413
[/td]
[td]Cust X
[/td]
[td]2018-BAT-01
[/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td]185.162
[/td]
[/tr]
[TD="align: center"]#VALUE![/TD]
[tr]
[td]123460
[/td]
[td]23456
[/td]
[td]34656
[/td]
[td]4898780
[/td]
[td]Cust X
[/td]
[td]TASK0096028
[/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td]176.776
[/td]
[/tr]
[TD="align: center"]#VALUE![/TD]
[tr]
[td]123461
[/td]
[td]23456
[/td]
[td]34656
[/td]
[td]5350076
[/td]
[td]Cust X
[/td]
[td]FR2005-7922-64233
[/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td]1362.232
[/td]
[/tr]
[TD="align: center"]#VALUE![/TD]
[tr]
[td]123462
[/td]
[td]23456
[/td]
[td]34656
[/td]
[td]810653
[/td]
[td]Cust X
[/td]
[td]18-002-007
[/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td]271.218
[/td]
[/tr]
[TD="align: center"]#VALUE![/TD]
[tr]
[td]123463
[/td]
[td]23456
[/td]
[td]34656
[/td]
[td]5996507
[/td]
[td]Cust X
[/td]
[td]1803020002
[/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td]1095.011
[/td]
[/tr]
[TD="align: right"]1803021097[/TD]
[tr]
[td]123464
[/td]
[td]23456
[/td]
[td]34656
[/td]
[td]6210799
[/td]
[td]Cust X
[/td]
[td]FR0135-6702-96457
[/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td]143.792
[/td]
[/tr]
[TD="align: center"]#VALUE![/TD]
[tr]
[td]123465
[/td]
[td]23456
[/td]
[td]34656
[/td]
[td]4577224
[/td]
[td]Cust X
[/td]
[td]34934093
[/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td]85.996
[/td]
[/tr]
[TD="align: right"]34934179[/TD]
[tr]
[td]123466
[/td]
[td]23456
[/td]
[td]34656
[/td]
[td]3046773
[/td]
[td]Cust X
[/td]
[td]2286071,VPN:P4317Q
[/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td]3555.615
[/td]
[/tr]
[TD="align: center"]#VALUE![/TD]
[tr]
[td]123467
[/td]
[td]23456
[/td]
[td]34656
[/td]
[td]3046773
[/td]
[td]Cust X
[/td]
[td]2286072,VPN:593-10294
[/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td]235.1
[/td]
[/tr]
[TD="align: center"]#VALUE![/TD]
[tr]
[td]123468
[/td]
[td]23456
[/td]
[td]34656
[/td]
[td]3046773
[/td]
[td]Cust X
[/td]
[td]2286073,VPN:593-10295
[/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td]117.55
[/td]
[/tr]
[TD="align: center"]#VALUE![/TD]
[tr]
[td]123469
[/td]
[td]23456
[/td]
[td]34656
[/td]
[td]3046773
[/td]
[td]Cust X
[/td]
[td]2286074,VPN:593-11145
[/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td]52.042
[/td]
[/tr]
[TD="align: center"]#VALUE![/TD]
[tr]
[td]123470
[/td]
[td]23456
[/td]
[td]34656
[/td]
[td]2994754
[/td]
[td]Cust X
[/td]
[td]4502596934SAP3034834
[/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td]563.124
[/td]
[/tr]
[TD="align: center"]#VALUE![/TD]
[tr]
[td]123471
[/td]
[td]23456
[/td]
[td]34656
[/td]
[td]2003147
[/td]
[td]Cust X
[/td]
[td]2100563077
[/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td]3870.016
[/td]
[/tr]
[TD="align: right"]2100566947[/TD]
[tr]
[td]123472
[/td]
[td]23456
[/td]
[td]34656
[/td]
[td]2995777
[/td]
[td]Cust X
[/td]
[td]PO65857
[/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td]2020.12
[/td]
[/tr]
[TD="align: center"]#VALUE![/TD]
[/TABLE] -
-
Yes Correct. Is that my issue PO is provided by customer so he can be alpha numeric or text etc ..
This PO number is spitted and process into 2 orders 123456 (Rvn 3566) & 123457 (4986). I want that macro to highlight Revn and PO Column in Red if less than or in green if more than or equal 40KOrd Nbr Account Bilt Bilt Name Po Revn 123455 23456 34656 2568069 Cust X FR256806 3555.615 123456 23456 34656 3115133 Cust X 2018 03 07 3566 123457 23456 34656 3115133 Cust X 2018 03 07 4986 123458 23456 34656 5175186 Cust X NVBY98S 1125.668 123459 23456 34656 3590413 Cust X 2018-BAT-01 185.162 123460 23456 34656 4898780 Cust X TASK0096028 176.776 123461 23456 34656 5350076 Cust X FR2005-7922-64233 1362.232 123462 23456 34656 810653 Cust X 18-002-007 271.218 123463 23456 34656 5996507 Cust X 1803020002 1095.011 123464 23456 34656 6210799 Cust X FR0135-6702-96457 143.792 123465 23456 34656 4577224 Cust X 34934093 85.996 123466 23456 34656 3046773 Cust X 2286071,VPN:P4317Q 3555.615 123467 23456 34656 3046773 Cust X 2286072,VPN:593-10295 23545.1 123468 23456 34656 3046773 Cust X 2286073,VPN:593-10295 1171.55 123469 23456 34656 3046773 Cust X 2286074,VPN:593-11145 52.042 123470 23456 34656 2994754 Cust X 4502596934SAP3034834 563.124 123471 23456 34656 2003147 Cust X 2100563077 3870.016 123472 23456 34656 2995777 Cust X PO65857 2020.12
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!