Split Upper / Lower case + concatenate rows

I'm looking for a solution for several issues I have with a copied data set.

Attachment 1673

The data set is as per column A and B in above screenshot.
The problem in column A is that it contains the unique ID number but also shows blanks. It's not containing always the same number of blanks until the next ID number.
(I have approx 4000 ID's in the dataset).

The problem in Column B is that it contains both upper case and lower case characters. I have found on this forum several ways how to extract those, but my problem is that I need to concatenate all lower cases together. In the end I need 2 seperate columns:
Column D may contain only the upper case characters from column B and has to concatenate those from multiple cells in the column within the ID range (column A).
Column E is simular, but here I should concatenate all lower case characters from multiple cells in column B, within the ID range (column A).

Who would be able to help me with this?

Thanks !!!

https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
https://www.youtube.com/@alanelston2330
https://www.youtube.com/watch?v=yXaYszT11CA&lc=UgxEjo0Di9-9cnl8UnZ4AaABAg.9XYLEH1OwDIA35HNIei0z-
https://eileenslounge.com/viewtopic.php?p=316154#p316154
https://www.youtube.com/watch?v=TW3l7PkSPD4&lc=UgwAL_Jrv7yg7WWC8x14AaABAg
https://teylyn.com/2017/03/21/dollarsigns/#comment-191
https://eileenslounge.com/viewtopic.php?p=317050#p317050
https://eileenslounge.com/viewtopic.php?f=27&t=40953&p=316854#p316854
https://www.eileenslounge.com/viewtopic.php?v=27&t=40953&p=316875#p316875
https://eileenslounge.com/viewtopic.php?p=316057#p316057
https://eileenslounge.com/viewtopic.php?p=316705#p316705
https://eileenslounge.com/viewtopic.php?p=316704#p316704
https://eileenslounge.com/viewtopic.php?p=176255#p176255
https://eileenslounge.com/viewtopic.php?f=27&t=40919&p=316597#p316597
https://eileenslounge.com/viewtopic.php?p=316412#p316412
https://eileenslounge.com/viewtopic.php?p=316254#p316254
https://eileenslounge.com/viewtopic.php?p=316280#p316280
https://eileenslounge.com/viewtopic.php?p=315915#p315915
https://eileenslounge.com/viewtopic.php?p=315512#p315512
https://eileenslounge.com/viewtopic.php?p=315744#p315744
https://www.eileenslounge.com/viewtopic.php?p=315512#p315512
https://eileenslounge.com/viewtopic.php?p=315680#p315680
https://eileenslounge.com/viewtopic.php?p=315743#p315743
https://www.eileenslounge.com/viewtopic.php?p=315326#p315326
https://www.eileenslounge.com/viewtopic.php?f=30&t=40752
https://eileenslounge.com/viewtopic.php?p=314950#p314950
https://www.eileenslounge.com/viewtopic.php?p=314940#p314940
https://www.eileenslounge.com/viewtopic.php?p=314926#p314926
https://www.eileenslounge.com/viewtopic.php?p=314920#p314920
https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=314837#p314837
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA

see attached.

https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
https://www.eileenslounge.com/viewtopic.php?f=44&t=40455&p=313035#p313035
https://www.eileenslounge.com/viewtopic.php?f=18&t=40411&p=312889#p312889
https://www.eileenslounge.com/viewtopic.php?f=18&t=40411&p=312886#p312886
https://www.eileenslounge.com/viewtopic.php?f=18&t=40411&p=312752#p312752
https://www.eileenslounge.com/viewtopic.php?f=18&t=40411&p=312734#p312734
https://www.eileenslounge.com/viewtopic.php?f=18&t=40411&p=312727#p312727
https://www.eileenslounge.com/viewtopic.php?f=18&t=40411&p=312724#p312724
https://www.eileenslounge.com/viewtopic.php?f=44&t=40374&p=312535#p312535
https://www.eileenslounge.com/viewtopic.php?p=312533#p312533
https://www.eileenslounge.com/viewtopic.php?f=44&t=40373&p=312499#p312499
https://www.youtube.com/watch?v=jTmVtPHtiTg&lc=Ugy_RiNN_kAqUvZ8W994AaABAg. 9xhyRrsUUOM9xpn-GDkL3o
https://www.youtube.com/watch?v=jTmVtPHtiTg&lc=Ugy_RiNN_kAqUvZ8W994AaABAg
https://www.youtube.com/watch?v=jTmVtPHtiTg&lc=UgzlC5LBazG6SMDP4nl4AaABAg
https://www.youtube.com/watch?v=jTmVtPHtiTg&lc=UgzlC5LBazG6SMDP4nl4AaABAg. 9zYoeePv8sZ9zYqog9KZ5B
https://www.youtube.com/watch?v=jTmVtPHtiTg&lc=Ugy_RiNN_kAqUvZ8W994AaABAg. 9xhyRrsUUOM9zYlZPKdOpm
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA

Code:

---

Sub M_snb()
    ReDim sp(1 To Cells(1).CurrentRegion.Columns(1).SpecialCells(4).Areas.Count, 1 To 2)
    y = 1
   
    For Each ar In Cells(1).CurrentRegion.Columns(1).SpecialCells(4).Areas
        c00 = Join(Application.Transpose(ar.Offset(-1, 1).Resize(ar.Rows.Count + 1)))
        For j = 1 To Len(c00)
          If Mid(c00, j, 1) Like "[a-z]" Then
                sp(y, 1) = Left(c00, j - 1)
                sp(y, 2) = Mid(c00, j)
                y = y + 1
                Exit For
            End If
        Next
    Next
   
    Cells(20, 4).Resize(UBound(sp), UBound(sp, 2)) = sp
End Sub

---

Quote:

---

Originally Posted by snb

Code:

---

Sub M_snb()
    ReDim sp(1 To Cells(1).CurrentRegion.Columns(1).SpecialCells(4).Areas.Count, 1 To 2)
    y = 1
   
    For Each ar In Cells(1).CurrentRegion.Columns(1).SpecialCells(4).Areas
        c00 = Join(Application.Transpose(ar.Offset(-1, 1).Resize(ar.Rows.Count + 1)))
        For j = 1 To Len(c00)
          If Mid(c00, j, 1) Like "[a-z]" Then
                sp(y, 1) = Left(c00, j - 1)
                sp(y, 2) = Mid(c00, j)
                y = y + 1
                Exit For
            End If
        Next
    Next
   
    Cells(20, 4).Resize(UBound(sp), UBound(sp, 2)) = sp
End Sub

---

---

thanks for the effort, but in my real data set I also have ID's without blank cells below and this code isn't picking those up.

Quote:

---

Originally Posted by p45cal

see attached.

---

super, this looks what I want to see.
I'm new with this, and run into following issue:
I copied my real dataset into column A and B and also added an extra number on the bottom.
Then I change this part of the formula (2 parts) to the last cell number in my dataset. but then I get following error message:

"You cannot change part of an array"

how can I solve this?

The arrays are only 2 cells wide. You select the two cells, adjust the formula, then ctrl+shift+Enter to enter the formula into both cells. Then you can copy the pair of cells down.

Please do not quote !

Why didn't you tell so in the first place ?

thanks p45cal

@snb, my appologies, I thought it didn't make a difference.
apperently seen the other solution, it doesn't.

It does; but you can only take it into account if the information doesn't lack.

Code:

---

Sub M_snb()
    ReDim sp(1 To Cells(1).CurrentRegion.Columns(1).SpecialCells(2).Count, 1 To 2)
    y = 1
   
    For Each cl In Cells(1).CurrentRegion.Columns(1).Offset(1).SpecialCells(2)
        c00 = cl.Offset(, 1)
        If cl.Offset(1) = "" Then c00 = Join(Application.Transpose(cl.Offset(, 1).Resize(cl.Resize(UBound(sp)).SpecialCells(4).Areas(1).Cells.Count + 1)))
       
        For j = 1 To Len(c00)
          If Mid(c00, j, 1) Like "[a-z]" Then
                sp(y, 1) = Left(c00, j - 1)
                sp(y, 2) = Mid(c00, j)
                y = y + 1
                Exit For
            End If
        Next
    Next
   
    Cells(20, 4).Resize(UBound(sp), UBound(sp, 2)) = sp
End Sub

---

@SNB got an error running this code. but got the other one working in this thread, so thanks for your efforts!
@p45cal: thanks! it worked, you saved me a week of work!!!! much appreciated.

If reporting any 'error' please be specific (line, message, error codenumber, error message, etc) about it, otherwise it's no information.

no error. found it already where I needed to make an adjustment. it works. thanks !