CODEWITHSUNDEEP
excelexcel-formulapivot-table
Mat Pike
Mat Pike

Reputation: 1

How to merge/import new data into Excel sheet with multiple matching columns

I have monthly CSV files supplied from another program that I need to import into Excel and analyse the data to provide monthly and year to date reports.

The difficulty I am having is twofold. The report columns do not align month to month and the program that generates them repeats column names for matching data.

File 1:
Title | Label | Label | Label | Date | ....
Stuff | Mice | Cats | Dogs | 01/01/2020 | ....

File 2:
Title | Label | Label | Date | ....
Stuff | Birds | Fish | 02/02/2020 | ....

As you can see month 2, there was 1 less label and therefore one less column, however if i concatenate them into 1 field then how do I analyse the number of each label used per month?

I have tried to import them as queries from CSV, and adding to the data model then making a pivot table, but there are approx 100 columns each month and then excel bugs out and runs out of memory.

Ideally I would end up with a table/graph that shows (and allows me to import each month without needing to format tables:

Label | Jan | Feb | and so on ...
Mice  | 7   | 0   |
Cats  | 1   | 2   |
Dogs  | 0   | 5   |
Fish  | 3   | 92  |

and so on.........

Upvotes: 0

Views: 136

Answers (1)

Mat Pike
Mat Pike

Reputation: 1

OK, so i figured it out. Rather than trying to import it and it stuff up (encapsulated comma's aplenty) I decided to open in the background and copy worksheet into current workbook.

Like this....

Sub IMPORT_MONTH()

    Dim fileName As Variant

    'open dialogue box to get new file to import
    fileName = Application.GetOpenFilename
            
    If fileName = False Then
        MsgBox ("Why did you click cancel?")
        Exit Sub
    End If
            
    On Error GoTo ErrHandler
    
    ' run update in background
    Application.ScreenUpdating = False

    Set closedBook = Workbooks.Open(fileName)
    closedBook.Sheets(1).Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
    closedBook.Close SaveChanges:=False

    Application.ScreenUpdating = True
    
    'move cursor to home for ease of readability
    ActiveSheet.Range("A1").Select
    
Exit Sub

ErrHandler:
    MsgBox ("It Broke")

End Sub

Upvotes: 0

Related Questions