CODEWITHSUNDEEP
vbaexcel
udidosa
udidosa

Reputation: 135

Excel VBA - Grouping list of strings in one cell

I don't know how to best describe this but it's better that I explain my problem in pictures.

I have 2 worksheets:

In worksheet Array, there are certain periods with their corresponding 'Array' associated with them.

Worksheet Array

In Sheet1, there is a list of strings in the format: dd/mm/yyyy hh:mm:ss AM/PM - # ordered by ascending order of number, then by date and finally by time.

Sheet1

The code I have, generates those values in Sheet1 by extracting the data in Array and listing them out in one cell. The code I've used is.

Sub Filter()
Const Array_FirstRow As Integer = 2 'Indicates the first row (row 2) in Array sheet
Dim Array_RowIndex As Integer 'variable to loop through array values of col A

Dim Summary_PeriodMoment1 As String 'in worksheet Sheet 1
Array_RowIndex = Array_FirstRow
Array_LastRow = Array_RowIndex - 1
Summary_PeriodMoment1 = ""

For Array_RowIndex = Array_FirstRow To Array_LastRow
  If Summary_PeriodMoment1 <> " " Then
  Summary_PeriodMoment1 = Summary_PeriodMoment1 & ", " & Worksheets("Array").Cells(Array_RowIndex, Array_DateTime_Column).Value
  End If
Next
Sheet1.Cells(1, 1).Value = Summary_PeriodMoment1
End Sub

This is slightly confusing and overly complicated to read. Is there any way to add code to :

  1. Sort/group the values by # and consolidate by date (to make it less confusing)? Like so?

Sorted/Grouped by #

  1. Have a separate cell for each value, again categorized by # (I would like to plot these values on a pivot graph later on using other code, so would like it to be in a friendly format

desirable output

Essentially I would like to do some data reformatting/transposing with a VBA script. Any idea what functions I should use? thanks!

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++ Update: I have what I need for transposing a string of values in one cell. I wonder if this can be done for multiple cells. I tried using this code:

 Sub TextToRows()
'Code for 1.2. section
Dim Arr As Variant
Dim Arr1 As Variant
Dim Arr2 As Variant
Dim InputRng As Range, InputRng2 As Range, InputRng3 As Range, OutputRng As Range, OutputRng1 As Range, OutputRng2 As Range

Set InputRng = Range("B1") 'Cell Containing all the text
Set InputRng1 = Range("B2")
Set InputRng2 = Range("B3")
Set OutputRng = Range("D1") 'First Cell of a column that you want the output there
Set OutputRng1 = Range("G1")
Set OutputRng2 = Range("J1")

Arr = Split(InputRng.Value, ",")
Arr1 = Split(InputRng.Value, ",")
Arr2 = Split(InputRng.Value, ",")

Set OutputRng = OutputRng.Resize(UBound(Arr) - LBound(Arr) + 1)
OutputRng.Value = Application.Transpose(Arr)

Set OutputRng1 = OutputRng1.Resize(UBound(Arr1) - LBound(Arr1) + 1)
OutputRng.Value = Application.Transpose(Arr1)

Set OutputRng2 = OutputRng2.Resize(UBound(Arr2) - LBound(Arr2) + 1)
OutputRng.Value = Application.Transpose(Arr2)

End Sub

Seems it only works for InputRng and not InputRng1 or InputRng2 enter image description here

Upvotes: 1

Views: 1513

Answers (1)

M--
M--

Reputation: 29109

1. How to split comma-delimited data in one cell? (Look below)

1.1. If you don't have any other data, and number of records are not more than number of possible columns in excel then transposing within the worksheet is an option (Instead of using the code below).

1.2. (If you have more data than limit of excel columns): Otherwise, you need to use arrays. The code below answers first part of your question. It will split the cell for "," as delimiter.

2. Then you can use Text to Columns in Data tab and delimiter ":" to get the numbers in one column and dates in another one.

3. Use How To Transpose Cells In One Column Based On Unique Values In Another Column? to group them based on the numbers.

Sub TextToRows()
'Code for 1.2. section
Dim Arr As Variant
Dim InputRng As Range, OutputRng As Range

Set InputRng = Range("B1") 'Cell Containing all the text

Set OutputRng = Range("D1") 'First Cell of a column that you want the output there

Arr = Split(InputRng.Value, ",")

Set OutputRng = OutputRng.Resize(UBound(Arr) - LBound(Arr) + 1)
OutputRng.Value = Application.Transpose(Arr)

End Sub

Upvotes: 1

Related Questions