vba - how do i store data without printing them on the spreadsheet

[noidea23]

hello all,

i have a huge set of raw data in one sheet and i'm trying to generate a summary of the raw data in another sheet.

One of the summaries requires me to obtain the counts of an object in a specific category and date. For example: 5 "English in" Jan 2021 --> i.e. i need to obtain the count of language in a particular month and year. So what i did was creating a new sheet, copy and paste the columns i need, count unique cells, remove duplicates, delimit, then concatenate the cells to obtain "5 English in Jan 2021".

My question is, is there a way to store the concatenated cells [Cells(i, 5) = Cells(i, 2) & " " & MonthName(Month(Cells(i, 3)), True) & " " & Year(Cells(i, 3))] without printing them on the spreadsheet. Because i do not wish to create a new spreadsheet just to tabulate the counts of the objects.

Your help will be greatly appreciated!

Sub uniKue()
Dim i As Long, N As Long, s As String, r As Range
N = Cells(Rows.Count, "A").End(xlUp).Row
For i = 2 To N
Sheets("Sheet2").Cells(i, 5) = Cells(i, 2) & " " & MonthName(Month(Cells(i, 3)), True) & " " & Year(Cells(i, 3))
Next i

Sheets("Sheet2").Range("E:E").Copy Sheets("Sheet2").Range("F:F")


Sheets("Sheet2").Range("F:F").RemoveDuplicates Columns:=1, Header:=xlNo
End Sub




Sub split()
Dim r As Range
Sheets("Sheet2").Select
For Each r In Sheets("Sheet2").Range("F:F").SpecialCells(2).Offset(, 1)
r.Formula = "=COUNTIF(E:E," & r.Offset(, -1).Address & ")"
Next r

Sheets("Sheet2").Range("G:G").Copy
Sheets("Sheet2").Range("I:I").PasteSpecial Paste:=xlPasteValues
Sheets("Sheet2").Range("G:H").Clear
Sheets("Sheet2").Range("E:E").Clear

Sheets("Sheet2").Range("F:F").TextToColumns , xlDelimited, Space:=True
End Sub




Sub concatenate()

 

[jolivanes]

"tabulate the counts of the objects"

Would that not be N - 1 ?

 

[noidea23]

I'm not sure what you meant by N - 1

i have tried storing the list as "temp" below but when printed on the sheet, it only shows the last value instead of the entire i=2 to N list. So, i need help in storing the list.

For i = 2 To N
temp = Array(Cells(i, 2) & " " & MonthName(Month(Cells(i, 3)), True) & " " & Year(Cells(i, 3)))

Next i

Sheets("Sheet1").Range("O1") = temp

 

[jolivanes]

"Because i do not wish to create a new spreadsheet just to tabulate the counts of the objects."

You want to this because of the above?

 

[jolivanes]

Maybe you could formulate your request differently.
Something like

For each c In Range("B1:B" & Cells(Rows.Count, 2).End(xlUp).Row
If c has "English" as part of the value in that cell and the date in cell to the right is "March" Then
do something
Else
do nothing
Next c