Trying to clear certain columns if a name is present

zone709

Well-known Member
Joined
Mar 1, 2016
Messages
2,137
Office Version
  1. 365
Platform
  1. Windows
Hi trying to clear other columns other then if 523 9th ave is in H,L,P,T,X,AA,AD. As you can see i also need to keep the data from 3 columns before the name 523 9th ave. If anything other then 523 9th ave is present i need to clear it from the range only E to Ad.


Example:
Book1
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAG
1SNOEmployee NameLocal\TradeIDMONDAYTUESDAYWEDNESDAYTHURSDAYFRIDAYSATURDAYSUNDAYTOTAL
24/27/20204/28/20204/29/20204/30/20205/1/20205/2/20205/3/2020
3STOTDTJOBSTOTDTJOBSTOTDTJOBSTOTDTJOBSTOTDTJOBSTOTJOBSTOTJOBSTOTDT
41JoeOffice18.001.002.00523 9th ave8.001.002.00
52MikeLaborer BK28.00523 9th ave8.000.000.00
63EdOffice38.002.003.00104 Ralph Ave8.002.003.00104 Ralph Ave8.001.001.00523 9th ave24.005.007.00
74RalphOffice48.00523 9th ave8.000.000.00
85LeeOffice78.001.001.50104 Ralph Ave8.001.001.00523 9th ave16.002.002.50
96TommyOffice118.001.001.00104 Ralph Ave8.00523 9th ave16.001.001.00
10Totals80.009.0012.50
11Overall Totals101.50
Data
Named Ranges
NameRefers ToCells
Print_Area=Data!$A$1:$AH$70AE4:AG10, AE11
Print_Titles=Data!$1:$3E2:AD2



Results:

Book1
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACAD
1SNOEmployee NameLocal\TradeIDMONDAYTUESDAYWEDNESDAYTHURSDAYFRIDAYSATURDAYSUNDAY
24/27/20204/28/20204/29/20204/30/20205/1/20205/2/20205/3/2020
3STOTDTJOBSTOTDTJOBSTOTDTJOBSTOTDTJOBSTOTDTJOBSTOTJOBSTOTJOB
41JoeOffice18.001.002.00523 9th ave
52MikeLaborer BK28.00523 9th ave
63EdOffice38.001.001.00523 9th ave
74RalphOffice48.00523 9th ave
85LeeOffice78.001.001.00523 9th ave
96TommyOffice118.00523 9th ave
Data
Named Ranges
NameRefers ToCells
Print_Titles=Data!$1:$3E2:AD2
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
You're welcome & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,226,730
Messages
6,192,711
Members
453,748
Latest member
akhtarf3

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