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

noidea23

New Member
Joined
Feb 16, 2022
Messages
28
Office Version
  1. 2021
Platform
  1. Windows
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!
1646017231227.png


1646017211020.png


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

Attachments

  • 1646014603869.png
    1646014603869.png
    8.3 KB · Views: 6

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

jolivanes

Well-known Member
Joined
Sep 5, 2004
Messages
2,107
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
"tabulate the counts of the objects"

Would that not be N - 1 ?
 

noidea23

New Member
Joined
Feb 16, 2022
Messages
28
Office Version
  1. 2021
Platform
  1. Windows
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

Well-known Member
Joined
Sep 5, 2004
Messages
2,107
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
"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

Well-known Member
Joined
Sep 5, 2004
Messages
2,107
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
Maybe you could formulate your request differently.
Something like
VBA Code:
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
 

Forum statistics

Threads
1,176,119
Messages
5,901,480
Members
434,896
Latest member
Derquila

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top