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()
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()