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


[TABLE="class: head"]
<tbody>[TR]
[TH][/TH]
[TH][/TH]
[TH][/TH]
[TH][/TH]
[TH][/TH]
[TH][/TH]
[TH][/TH]
[TH][/TH]
[TH][/TH]
[TH][/TH]
[TH][/TH]
[/TR]
[TR]
[TD]

​

[/TD]
[TD]EW Asset Name[/TD]
[TD]Equipment Type[/TD]
[TD]ESN[/TD]
[TD]Device MIN[/TD]
[TD]FW[/TD]
[TD]Last Transmitted[/TD]
[TD]Device Model[/TD]
[TD]Currently Coupled[/TD]
[TD]First Coupled[/TD]
[TD]Audit Notes[/TD]
[/TR]
[TR]
[TD]

​

[/TD]
[TD]

166801​

[/TD]
[TD]Smooth Roller[/TD]
[TD]C5S00210[/TD]
[TD][/TD]
[TD]-[/TD]
[TD]Dec 15 2017 05:54AM[/TD]
[TD]AEMP[/TD]
[TD]Jun 01 2014 12:00AM[/TD]
[TD]Jun 01 2014 12:00AM[/TD]
[TD]Last transmission was AEMP on 12/15/2017, is the device still installed?[/TD]
[/TR]
[TR]
[TD]

​

[/TD]
[TD]

169200​

[/TD]
[TD]Scraper[/TD]
[TD]DBW00130[/TD]
[TD][/TD]
[TD]-[/TD]
[TD]Dec 15 2017 11:43AM[/TD]
[TD]AEMP[/TD]
[TD]Jun 01 2014 12:00AM[/TD]
[TD]Jun 01 2014 12:00AM[/TD]
[TD]Last transmission was AEMP on 12/15/2017, is the device still installed?[/TD]
[/TR]
[TR]
[TD]

​

[/TD]
[TD]

169201​

[/TD]
[TD]Scraper[/TD]
[TD]DBW00131[/TD]
[TD][/TD]
[TD]-[/TD]
[TD]Dec 15 2017 11:44AM[/TD]
[TD]AEMP[/TD]
[TD]Jun 01 2014 12:00AM[/TD]
[TD]Jun 01 2014 12:00AM[/TD]
[TD]Last transmission was AEMP on 12/15/2017, is the device still installed?[/TD]
[/TR]
[TR]
[TD]

​

[/TD]
[TD]

141508​

[/TD]
[TD]Wheel Loader[/TD]
[TD]

4642007212​

[/TD]
[TD]A100003690ABCB[/TD]
[TD]

2.82​

[/TD]
[TD]Jan 10 2018 01:57PM[/TD]
[TD]GS-280[/TD]
[TD]May 12 2015 12:00AM[/TD]
[TD]May 12 2015 12:00AM[/TD]
[TD]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[/TD]
[/TR]
[TR]
[TD]

​

[/TD]
[TD]

18200​

[/TD]
[TD]Transport Detach Trailer[/TD]
[TD]

3143037674​

[/TD]
[TD]A10000438633CE[/TD]
[TD]

7.4​

[/TD]
[TD]Dec 28 2017 04:10PM[/TD]
[TD]GS-720t[/TD]
[TD]Oct 20 2015 12:00AM[/TD]
[TD]Oct 20 2015 12:00AM[/TD]
[TD]Last transmission was Initial Power Up on 12/28/2017, is the device still installed?[/TD]
[/TR]
[TR]
[TD]

​

[/TD]
[TD]

19227​

[/TD]
[TD]Enclosed Cargo Trailer[/TD]
[TD]

3143038420​

[/TD]
[TD]A10000438640DE[/TD]
[TD]-[/TD]
[TD]Jan 10 2018 05:50PM[/TD]
[TD]GS-720t[/TD]
[TD]Oct 28 2015 12:00AM[/TD]
[TD]Oct 28 2015 12:00AM[/TD]
[TD]Last transmission was Heartbeat Sleep on 01/10/2018, is the device still installed?[/TD]
[/TR]
[TR]
[TD]

​

[/TD]
[TD]

19221​

[/TD]
[TD]Enclosed Cargo Trailer[/TD]
[TD]

3143046224​

[/TD]
[TD]A1000043849219[/TD]
[TD]

7.4​

[/TD]
[TD]Sep 12 2017 12:00PM[/TD]
[TD]GS-720t[/TD]
[TD]Dec 03 2015 12:00AM[/TD]
[TD]Dec 03 2015 12:00AM[/TD]
[TD]Last transmission was Initial Power Up on 09/12/2017, is the device still installed?[/TD]
[/TR]
[TR]
[TD]

​

[/TD]
[TD]

101002​

[/TD]
[TD]Truck Crane[/TD]
[TD]

4642015384​

[/TD]
[TD]A100004386D792[/TD]
[TD]

2.83​

[/TD]
[TD]Jan 10 2018 04:26PM[/TD]
[TD]GS-280[/TD]
[TD]Jun 17 2016 12:00AM[/TD]
[TD]Oct 07 2015 12:00AM[/TD]
[TD]Last transmission was Transition to Sleep on 01/10/2018, is the device still installed?[/TD]
[/TR]
[TR]
[TD]

​

[/TD]
[TD]

10265​

[/TD]
[TD]All Terrain Crane[/TD]
[TD]

4642018313​

[/TD]
[TD]A100004386C658[/TD]
[TD]

2.83​

[/TD]
[TD]Nov 15 2017 10:20AM[/TD]
[TD]GS-280[/TD]
[TD]Dec 31 2015 12:00AM[/TD]
[TD]Dec 31 2015 12:00AM[/TD]
[TD]Last transmission was Transition to Sleep on 11/15/2017, is the device still installed?[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: grid"]
<tbody>[TR]
[TD]Sheet: device_audit_0142018015145[/TD]
[/TR]
</tbody>[/TABLE]

 

[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