IMPORTRANGE not working in Excel - can anyone help pls?

[tcjotm]

Hello!

I have been using google sheets but I now want to use Excel instead. I am using Excel 2010 with Win 8.1 and Chrome all on desktop.

I had a formula made for me to display some data in a certain way, but it doesn't seem to work in Excel. Can anyone help with the Excel version please?

I have uploaded the example xlsx to my google drive - link is here if you wish to download to make it easier : https://drive.google.com/file/d/1RJgNbFZtTsXJnNS3AawVK3qpGq2v3Aq-/view?usp=sharing

Sheet 1 is my raw data.
Sheet 2 is a paste of my current google sheets formula, minus the first '=' so it displays as text and not a broken formula.
Sheet 3 is what it sheet 1 looks like on google sheets after being processed by the formula.

The formula should be fairly self-explanatory but in summary :

1. Columns C to J are the only ones of relevance from sheet 1 - ignore the data in all the other columns, including column D.
2. Data from column C should be displayed without the letter suffix.
3. If Column J contains a '>' symbol, display a 'D' on sheet3.
4. If Column H contains EHRD, show DEP in column B on sheet3.
5. If Column I contains EHRD, show ARR in column B on sheet3.
6. If both columns H and I contain EHRD, show RT in column B on sheet3.

For those members not wishing to download the xlsx, here is the google sheet formula which pulls from a source google sheet :

[COLOR=#000000][FONT=Inconsolata]=[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]{[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]{[/FONT][/COLOR][COLOR=green][FONT=Inconsolata]"TIME"[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata],[/FONT][/COLOR][COLOR=green][FONT=Inconsolata]"STS"[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata],[/FONT][/COLOR][COLOR=green][FONT=Inconsolata]"CALLSIGN"[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata],[/FONT][/COLOR][COLOR=green][FONT=Inconsolata]"TYPE"[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata],[/FONT][/COLOR][COLOR=green][FONT=Inconsolata]"REG"[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata],[/FONT][/COLOR][COLOR=green][FONT=Inconsolata]"DIV"[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]}[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata];[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]ArrayFormula[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]([/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]REGEXREPLACE[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]([/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]Query[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]([/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]importrange[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]([/FONT][/COLOR][COLOR=green][FONT=Inconsolata]"https://docs.google.com/spreadsheets/d/1a8xduxzUwoioGkKWF-35b_kJoWIRsZCDz9doaHuaAks/edit#gid=1521280153"[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata],[/FONT][/COLOR][COLOR=green][FONT=Inconsolata]"EHRD!C1:J"[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata])[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata],[/FONT][/COLOR][COLOR=green][FONT=Inconsolata]"Select Col1"[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata])[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata],[/FONT][/COLOR][COLOR=green][FONT=Inconsolata]".\z"[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata],[/FONT][/COLOR][COLOR=green][FONT=Inconsolata]""[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata])[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata])[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata],[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]ArrayFormula[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]([/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]if[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]([/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]Query[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]([/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]importrange[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]([/FONT][/COLOR][COLOR=green][FONT=Inconsolata]"https://docs.google.com/spreadsheets/d/1a8xduxzUwoioGkKWF-35b_kJoWIRsZCDz9doaHuaAks/edit#gid=1521280153"[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata],[/FONT][/COLOR][COLOR=green][FONT=Inconsolata]"EHRD!C1:J"[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata])[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata],[/FONT][/COLOR][COLOR=green][FONT=Inconsolata]"Select Col6"[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata])[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]&[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]Query[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]([/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]importrange[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]([/FONT][/COLOR][COLOR=green][FONT=Inconsolata]"https://docs.google.com/spreadsheets/d/1a8xduxzUwoioGkKWF-35b_kJoWIRsZCDz9doaHuaAks/edit#gid=1521280153"[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata],[/FONT][/COLOR][COLOR=green][FONT=Inconsolata]"EHRD!C1:J"[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata])[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata],[/FONT][/COLOR][COLOR=green][FONT=Inconsolata]"Select Col7"[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata])[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]=[/FONT][/COLOR][COLOR=green][FONT=Inconsolata]"EHRDEHRD"[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata],[/FONT][/COLOR][COLOR=green][FONT=Inconsolata]"RT"[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata],[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]IF[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]([/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]Query[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]([/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]importrange[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]([/FONT][/COLOR][COLOR=green][FONT=Inconsolata]"https://docs.google.com/spreadsheets/d/1a8xduxzUwoioGkKWF-35b_kJoWIRsZCDz9doaHuaAks/edit#gid=1521280153"[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata],[/FONT][/COLOR][COLOR=green][FONT=Inconsolata]"EHRD!C1:J"[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata])[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata],[/FONT][/COLOR][COLOR=green][FONT=Inconsolata]"Select Col6"[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata])[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]=[/FONT][/COLOR][COLOR=green][FONT=Inconsolata]"EHRD"[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata],[/FONT][/COLOR][COLOR=green][FONT=Inconsolata]"DEP"[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata],[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]IF[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]([/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]Query[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]([/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]importrange[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]([/FONT][/COLOR][COLOR=green][FONT=Inconsolata]"https://docs.google.com/spreadsheets/d/1a8xduxzUwoioGkKWF-35b_kJoWIRsZCDz9doaHuaAks/edit#gid=1521280153"[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata],[/FONT][/COLOR][COLOR=green][FONT=Inconsolata]"EHRD!C1:J"[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata])[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata],[/FONT][/COLOR][COLOR=green][FONT=Inconsolata]"Select Col7"[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata])[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]=[/FONT][/COLOR][COLOR=green][FONT=Inconsolata]"EHRD"[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata],[/FONT][/COLOR][COLOR=green][FONT=Inconsolata]"ARR"[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata],[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata])[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata])[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata])[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata])[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata],[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]Query[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]([/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]importrange[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]([/FONT][/COLOR][COLOR=green][FONT=Inconsolata]"https://docs.google.com/spreadsheets/d/1a8xduxzUwoioGkKWF-35b_kJoWIRsZCDz9doaHuaAks/edit#gid=1521280153"[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata],[/FONT][/COLOR][COLOR=green][FONT=Inconsolata]"EHRD!C1:J"[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata])[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata],[/FONT][/COLOR][COLOR=green][FONT=Inconsolata]"Select Col3, Col4, Col5"[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata])[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata],[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]ArrayFormula[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]([/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]if[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]([/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]Query[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]([/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]importrange[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]([/FONT][/COLOR][COLOR=green][FONT=Inconsolata]"https://docs.google.com/spreadsheets/d/1a8xduxzUwoioGkKWF-35b_kJoWIRsZCDz9doaHuaAks/edit#gid=1521280153"[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata],[/FONT][/COLOR][COLOR=green][FONT=Inconsolata]"EHRD!C1:J"[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata])[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata],[/FONT][/COLOR][COLOR=green][FONT=Inconsolata]"Select Col8"[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata])[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]=[/FONT][/COLOR][COLOR=green][FONT=Inconsolata]">"[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata],[/FONT][/COLOR][COLOR=green][FONT=Inconsolata]"D"[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata],[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata])[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata])[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]}[/FONT][/COLOR]

Is this possible to convert to Excel usage whereby the docs.google link is my Excel sheet1 and I wish for the 'end product' to be displayed on sheet2 of the same work book?

I would be extremely grateful to any kind person who can make some sense of this for me. I understand basic functions but I have to be honest, array formulas are well beyond my knowledge scope.

Thank you and rgds.

 

[tcjotm]

Option Explicit

Sub MrExcel_tcjotm()
    
    Dim lr As Long, r As Long
    Dim rng As Range, cel As Range
    Dim str As String


With Sheets("Sheet1")
    'determine last row used in col C
    lr = .Range("X" & Rows.Count).End(xlUp).Row
    'set the range to work with
    Set rng = .Range("X13:X" & lr)
End With


With Sheets("Sheet2")
    'Clear Sheet
    .Cells.Clear
    'the row to start on
    r = 1
    'put in headers
    .Range("A" & r).Resize(, 6).Value = Split("TIME STS CALLSIGN TYPE REG DIV")
    
    'work with each cell in rng
    For Each cel In rng
        'increment row
        r = r + 1
        'first col is the time
        str = str & "|" & Left(cel.Value, Len(cel.Value) - 1)
        
        'second col depends on values in H and I
        If cel.Offset(, 5) = "EHRD" And cel.Offset(, 6) = "EHRD" Then
            str = str & "|" & "RT"
        ElseIf cel.Offset(, 5) = "EHRD" And cel.Offset(, 6) <> "EHRD" Then
            str = str & "|" & "DEP"
        ElseIf cel.Offset(, 5) <> "EHRD" And cel.Offset(, 6) = "EHRD" Then
            str = str & "|" & "ARR"
        Else
            str = str & "|"
        End If
        
        'third, forth and fifth col is col E-G
        str = str & "|" & cel.Offset(, 2).Value & "|" & cel.Offset(, 3).Value & "|" & cel.Offset(, 4).Value
        
        'sixth col
        If cel.Offset(, 7).Value = ">" Then
            str = str & "|" & "D"
        Else
            str = str & "|"
        End If
        
        'write this data to the sheet
        .Range("A" & r).Resize(, 6).Value = Split(Mid(str, 2), "|")
        
        'reset str
        str = ""
    Next cel
End With
End Sub

Option Explicit


Sub Run_AllMacros()


Call Module1.MrExcel_tcjotm
Call Module2.MrExcel_tcjotm


End Sub

Can't just keep running the same macro as per your suggestion because the values relating to columns H & I are different on each sheet. It has to be a separate macro from each sheet, but it's all set up now and works great with a quick click of the 'run all macros' modules. All 30 sheet are done in a split second.