Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old February 1st 21, 10:31 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Oct 2018
Posts: 23
Default Split up or delineate data

I have a column of data similar to this:

ant
antique
art
bee
beautiful
bored
chores
dancing
daytime

Does Excel have any means of finding the rows where the first letter of the alphabet changes to a new letter, and insert a new row in between such as this:

----A----
ant
antique
art
----B----
bee
beautiful
bored
----C----
chores
----D---
dancing
daytime

etc. etc... I'm pretty much trying to insert letter headers above the start of each new letter series.

If not, what if I store the same sorted list of values in an array of strings? What would be a good way of creating a new array that inserts letter dividers similar to above?


  #2   Report Post  
Old February 1st 21, 10:50 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 3,848
Default Split up or delineate data

Hi,

Am Mon, 1 Feb 2021 01:31:17 -0800 (PST) schrieb Tatsujin:

I have a column of data similar to this:

ant
antique
art
bee
beautiful
bored
chores
dancing
daytime

Does Excel have any means of finding the rows where the first letter of the alphabet changes to a new letter, and insert a new row in between such as this:

----A----
ant
antique
art
----B----
bee
beautiful
bored
----C----
chores
----D---
dancing
daytime

etc. etc... I'm pretty much trying to insert letter headers above the start of each new letter series.


if your values are in column A then try:

Sub Test()
Dim LRow As Long, i As Long
Dim myStr As String

With ActiveSheet
LRow = .Cells(.Rows.Count, 1).End(xlUp).Row
For i = LRow To 2 Step -1
If Left(.Cells(i, 1), 1) < Left(.Cells(i - 1, 1), 1) Then
myStr = "---" & UCase(Left(.Cells(i, 1), 1)) & "---"
.Rows(i).Insert
.Cells(i, 1) = myStr
End If
Next
.Rows(1).Insert
.Cells(1, 1) = "---" & UCase(Left(.Cells(2, 1), 1)) & "---"
End With
End Sub

Otherwise change the references.


Regards
Claus B.
--
Windows10
Microsoft 365 for business
  #3   Report Post  
Old February 1st 21, 11:21 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Oct 2018
Posts: 23
Default Split up or delineate data


Wow, that is amazing!

I also wanted to find a solution where the data only existed in an array of strings or a variant, but I could probably just transfer the array to a spreadsheet column and run your code.

Thanks for your expertise!
  #4   Report Post  
Old February 1st 21, 12:11 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 3,848
Default Split up or delineate data

Hi,

Am Mon, 1 Feb 2021 02:21:00 -0800 (PST) schrieb Tatsujin:

I also wanted to find a solution where the data only existed in an array of strings or a variant, but I could probably just transfer the array to a spreadsheet column and run your code.


try:

Dim myStr As String
Dim varData As Variant

myStr = "ant,antique,art,bee,beautiful,bored,chores,dancin g,daytime"
varData = Split(myStr, ",")
Range("A1").Resize(UBound(varData) + 1) = Application.Transpose(varData)


Regards
Claus B.
--
Windows10
Microsoft 365 for business
  #5   Report Post  
Old February 1st 21, 09:11 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Oct 2018
Posts: 23
Default Split up or delineate data

try:

Dim myStr As String
Dim varData As Variant

myStr = "ant,antique,art,bee,beautiful,bored,chores,dancin g,daytime"
varData = Split(myStr, ",")
Range("A1").Resize(UBound(varData) + 1) = Application.Transpose(varData)

That basically just copied the string into column A. What I meant was, I was also thinking about a solution that doesn't involve spreadsheet cells.

For example, if

myStr = "ant,antique,art,bee,beautiful,bored,chores,dancin g,daytime"

then the output should be:

myStr2 = "--A--,ant,antique,art,--B--,bee, etc.."

But, I'm happy with your initial solution.

Thanks!


  #6   Report Post  
Old February 1st 21, 10:05 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 3,848
Default Split up or delineate data

Hi,

Am Mon, 1 Feb 2021 12:11:05 -0800 (PST) schrieb Tatsujin:

For example, if

myStr = "ant,antique,art,bee,beautiful,bored,chores,dancin g,daytime"

then the output should be:

myStr2 = "--A--,ant,antique,art,--B--,bee, etc.."


then try:

Sub Test()
Dim myStr As String
Dim varData() As Variant
Dim re, match, matches, ptrn
Dim n As Long, i As Long

Set re = CreateObject("vbscript.regexp")
ptrn = "\w+"

myStr = "ant,antique,art,bee,beautiful,bored,chores,dancin g,daytime"
re.pattern = ptrn
re.IgnoreCase = False
re.Global = True
Set matches = re.Execute(myStr)
ReDim Preserve varData(n)
varData(n) = "---" & UCase(Left(matches(1), 1)) & "---"
n = n + 1
For i = 0 To matches.Count - 2
ReDim Preserve varData(n)
If Left(matches(i + 1), 1) = Left(matches(i), 1) Then
varData(n) = matches(i)
n = n + 1
Else
varData(n) = matches(i)
n = n + 1
ReDim Preserve varData(n)
varData(n) = "---" & UCase(Left(matches(i + 1), 1)) & "---"
n = n + 1
End If
Next
ReDim Preserve varData(n)
varData(n) = matches(matches.Count - 1)
myStr = Join(varData, ", ")
Range("A1") = myStr
End Sub


Regards
Claus B.
--
Windows10
Microsoft 365 for business


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
split data swati Excel Discussion (Misc queries) 3 August 7th 07 10:46 AM
I'm Lost... Bin? Delineate different processes with time samples William Elerding Excel Programming 5 August 10th 06 06:39 AM
Split data and copy surrounding data dee Excel Programming 5 July 19th 06 01:23 PM
Split data into new sheets bernard Excel Discussion (Misc queries) 4 January 4th 06 05:45 PM
how to split data into columns and arrange the resulting data jack Excel Discussion (Misc queries) 1 November 12th 05 12:20 AM


All times are GMT +1. The time now is 02:43 AM.

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright 2004-2021 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017