Need Multiple Formulas for one Macro

PaigeWarner

New Member
Joined
May 27, 2020
Messages
44
Office Version
  1. 365
Platform
  1. MacOS
Hello I need formulas for the following parameters (in the below order) to create one Macro:

  1. Delete rows that do not start with a 47 in Purchasing Document (column D).
  2. Delete rows that do not have an ME730 Status message (column P).
  3. Delete rows that have ZSPC under Material Type (column F).
  4. Look at two columns together: Material Number (column J) and New Price (column N) and delete extra duplicate rows.

Here is a screen shot of how the file starts out:
1615561309417.png


And here is a screen shot of how it should end up (the rows that should be left after all the parameters used):
1615561382770.png



Here is a copy and paste of the rows and columns:
IDoc numberPartner numberVendor NamePurchasing DocumentItemMaterial TypeGen. item cat. grpMtrl Stock StatusMaterial CategoryMaterial NumberMIC NumberMaterial DescriptionOld PriceNew Price% PriceVarianceStatus message
79988937511121GHI Incorporated470399662711HAWAZNORStock02110104D567H2Grill Cleaner25.7026.462.96ME730
79988937111121GHI Incorporated47039966265HAWAZNORStock02110104D567H3Grill Cleaner25.7027.462.96ME730
79988857211121GHI Incorporated47039966241HAWAZNORStock02110104D567H4Grill Cleaner25.7026.462.96ME728
79988937711121GHI Incorporated51039966222HAWAZNORStock02110104D567H5Grill Cleaner25.7026.462.96ME730
79992539531415JKL Incorporated470399643121HAWAZNORStock14119219J1313RNIce Bucket8.839.062.60ME730
79992275831415JKL Incorporated470399643022HAWAZNORStock14119219J1313RNIce Bucket8.839.062.60ME730
79967931531415JKL Incorporated47039964278HAWAZNORStock14119219J1313RNIce Bucket8.839.062.60ME730
79975258931415JKL Incorporated470399642422HAWAZNORStock14119219J1313RNIce Bucket8.839.062.60ME728
79992539931415JKL Incorporated51039964218HAWAZNORStock14119219J1313RNIce Bucket8.839.062.60ME730
79964883216171MNO Incorporated47039960463HAWAZNORStock1413714827YH3RLandry Detergent117.64135.7015.35ME730
79964883216171MNO Incorporated47039960465ZSPCZNORStock1413715328YH3RWater Tank180.83207.4414.72ME730
79964883216171MNO Incorporated300399604611ZSPCZNORStock1413716529YH3RFloor Cleaner113.88141.3424.11ME730
79964883216171MNO Incorporated470399604612HAWAZNORStock1413716830YH3RBlender208.01222.206.82ME730
79964883216171MNO Incorporated47039960462HAWAZNORStock1413717726YH3RDish Soap101.84109.487.50ME790
80001892867891DEF Incorporated47039965901ZSPCZBN3Non Stock24443217H2312PFaucet Handle38.3440.254.98ME730


I know this is a lot at once so thank you so much in advance for any assistance you can provide!
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
1,174
Office Version
  1. 2016
Platform
  1. Windows
I wonder why the data row #2 was excluded. I think the row satisfy all conditions and the duplicate for it is in row #4 :unsure:
The first row should have no duplicate :giggle:
 

Watch MrExcel Video

Forum statistics

Threads
1,130,096
Messages
5,640,087
Members
417,126
Latest member
Jeffman52

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
Top