Potential Macro Question

[hellokirsten]

Hi all! Very happy to find this resource
I will try to be as thorough/concise as I can be

- I have a CSV file that we download for a number of customers. I have created a macro to remove all of the unnecessary information from that CSV file
- Now, there is a field of audit notes that I am trying to divide into these different sections: Idle (notes that contain "idle"), Hardware (notes that contain "battery, heartbeat, transition, and transport"), and Install (notes that contain "initial" or "engine")
- For those three different sections, I am wanting to build something that will take that full file, and divide it into three different sheets

I've tried to record a macro - but the line items range from 20 - 700, so the recording of the macro did not work very well.
Any help would be greatly appreciated!

 

[Fluff]

Hi & welcome to the board.
Could you supply an accurate sample of what your data look like?
There are some tools available here to aid in that
https://www.mrexcel.com/forum/about-board/508133-attachments.html

 

[hellokirsten]

Excel 2016 (Mac) 64 bit

​	EW Asset Name	Equipment Type	ESN	Device MIN	FW	Last Transmitted	Device Model	Currently Coupled	First Coupled	Audit Notes
​	166801​	Smooth Roller	C5S00210		-	Dec 15 2017 05:54AM	AEMP	Jun 01 2014 12:00AM	Jun 01 2014 12:00AM	Last transmission was AEMP on 12/15/2017, is the device still installed?
​	169200​	Scraper	DBW00130		-	Dec 15 2017 11:43AM	AEMP	Jun 01 2014 12:00AM	Jun 01 2014 12:00AM	Last transmission was AEMP on 12/15/2017, is the device still installed?
​	169201​	Scraper	DBW00131		-	Dec 15 2017 11:44AM	AEMP	Jun 01 2014 12:00AM	Jun 01 2014 12:00AM	Last transmission was AEMP on 12/15/2017, is the device still installed?
​	141508​	Wheel Loader	4642007212​	A100003690ABCB	2.82​	Jan 10 2018 01:57PM	GS-280	May 12 2015 12:00AM	May 12 2015 12:00AM	Last transmission was Transition to Sleep on 01/10/2018, is the device still installed? Equipment has been idle 71.43% during 01/04/2018 - 01/11/2018
​	18200​	Transport Detach Trailer	3143037674​	A10000438633CE	7.4​	Dec 28 2017 04:10PM	GS-720t	Oct 20 2015 12:00AM	Oct 20 2015 12:00AM	Last transmission was Initial Power Up on 12/28/2017, is the device still installed?
​	19227​	Enclosed Cargo Trailer	3143038420​	A10000438640DE	-	Jan 10 2018 05:50PM	GS-720t	Oct 28 2015 12:00AM	Oct 28 2015 12:00AM	Last transmission was Heartbeat Sleep on 01/10/2018, is the device still installed?
​	19221​	Enclosed Cargo Trailer	3143046224​	A1000043849219	7.4​	Sep 12 2017 12:00PM	GS-720t	Dec 03 2015 12:00AM	Dec 03 2015 12:00AM	Last transmission was Initial Power Up on 09/12/2017, is the device still installed?
​	101002​	Truck Crane	4642015384​	A100004386D792	2.83​	Jan 10 2018 04:26PM	GS-280	Jun 17 2016 12:00AM	Oct 07 2015 12:00AM	Last transmission was Transition to Sleep on 01/10/2018, is the device still installed?
​	10265​	All Terrain Crane	4642018313​	A100004386C658	2.83​	Nov 15 2017 10:20AM	GS-280	Dec 31 2015 12:00AM	Dec 31 2015 12:00AM	Last transmission was Transition to Sleep on 11/15/2017, is the device still installed?

<tbody>
</tbody>

Sheet: device_audit_0142018015145

<tbody>
</tbody>

 

[Fluff]

Couple of things,
1) what happens to Asset 141508, as that has both Transition & Idle, does it get copied to both sheets?
2) It is always a good idea to let people know that you are using a Mac. I was originally thinking of using a Dictionary, but AFAIK they don't work on Macs.

 

[hellokirsten]

Idle will trump transition for those cases, thanks for the quick responses!

 

[Fluff]

A few more questions
1) From your screen shot it looks a though col A & row 1 are blank with data starting in B2, is this correct?
2) Can we remove the data in the Audit notes column?
3) if the answer to 2 is no, can we copy that column to the first blank column & if so what is that column?

 

[hellokirsten]

1) that is not correct, when I used one of those tools to convert it to HTML, it kept bringing over these color cells. I tried to copy it without the color formatting which wasn't working either so I just deleted it after I pasted it in to the forum
2) The info in the audit notes in the most important, so we can't delete it.
3) I think this ties in to answer 1 since it's not really a blank column

 

[Fluff]

In that case does your data start in A1?
Re 3) what I meant was can we create a copy of the Audit Notes, in the first blank column after the data, so if the data goes to col K, can we use col J as a helper. If so, where does the data end?

 

[hellokirsten]

Yes it does!
The last column will always end at J as of now. (or K with the hypothetical helper column)

 

[Fluff]

OK, how about

Sub FilterToNewSheets()

   Dim SrcWS As Worksheet
   Dim CritAry As Variant
   Dim ShtAry As Variant
   Dim UsdRws As Long
   Dim Cnt As Long
   
   CritAry = Array("*idle*", "*battery*", "*heartbeat*", "*transition*", "*transport*", "*initial*", "*engine*")
   ShtAry = Array("Idle", "Hardware", "Hardware", "Hardware", "Hardware", "Install", "Install")
   Set SrcWS = ActiveSheet
   
   With SrcWS
      UsdRws = .Range("A" & Rows.Count).End(xlUp).Row
      .Columns(10).Copy .Columns(11)
   
      If .AutoFilterMode Then .AutoFilterMode = False
      For Cnt = LBound(CritAry) To UBound(CritAry)
         If Not shtexists(CStr(ShtAry(Cnt))) Then Sheets.Add(, Sheets(Sheets.Count)).Name = ShtAry(Cnt)
         .Range("A1:K1").AutoFilter 11, CritAry(Cnt)
         On Error Resume Next
         .Range("A2:J" & UsdRws).SpecialCells(xlVisible).Copy Sheets(ShtAry(Cnt)).Range("A" & Rows.Count).End(xlUp).Offset(1)
         .Range("K2:K" & UsdRws).SpecialCells(xlVisible).ClearContents
         On Error GoTo 0
      Next Cnt
      .AutoFilterMode = False
      .Columns(11).Clear
   End With
End Sub

Public Function shtexists(ShtName As String) As Boolean
    On Error Resume Next
    shtexists = (LCase(Sheets(ShtName).Name) = LCase(ShtName))
    On Error GoTo 0
End Function