0

I have a Google Sheet list of contact details. Unfortunately, they are all in a single Column.

Moreover, each set has a varying number of Rows.

I'd like to Transpose the Column but don't know how!

He is a sample of the current data:

enter image description here

Note that I've highlighted the final cell in each set in Green. And that each of those cells contains the word "Followers".

I'd like to Transpose based on that keyword, Followers, as being the final cell for each set.

So that my output would be something like this:

enter image description here

I'm sure there's a simple way to do it, but I'm at a total loss how!

Can anyone please help with the formula?

2
  • Please share a spreadsheet with realistic sample data for the volunteer contributors here to work with.
    – Erik Tyler
    Mar 21, 2022 at 18:50
  • @ErikTyler, you're right, I'm very sorry. I'm new to here and haven't quite worked out what I'm doing properly. Next time I shall. Thanks for the suggestion.
    – RoCrow
    Mar 22, 2022 at 9:12

1 Answer 1

0

You can try this formula with REGEX-TRANSPOSE-SPLIT:

=index(
  split(
   transpose(
     split(
      regexreplace(
       textjoin("~",1,A:A),
       "(Followers.*?)~",
       "$1%"),
     "%")),"~"))

...but depending on how big your dataset is, you may or may not get the following error:

enter image description here

So here's another possible solution:

=index(
  ifna(
   vlookup(
    if(
     sequence(1,20),
     sequence(countif(A:A,"Followers*"),1,0)
    )&sequence(1,20),
    {
     countifs(A:A,"Followers*",row(A:A)+1,"<="&row(A:A))&
     countifs(
      countifs(A:A,"Followers*",row(A:A)+1,"<="&row(A:A)),
      countifs(A:A,"Followers*",row(A:A)+1,"<="&row(A:A)),
      row(A:A),"<="&row(A:A)
     ),
     A:A
    },2,
   )
  )
 )

* If there are sets that contain more than 20 rows, increase the two occurrences of 20.

enter image description here

If you want a dragdown solution, use:

=transpose(
  filter(
   A:A,
   isbetween(
    row(A:A),
    index(filter({0;row(A:A)},regexmatch({"Followers";A:A},"Followers")),row(A1))+1,
    index(filter({0;row(A:A)},regexmatch({"Followers";A:A},"Followers")),row(A1)+1)
   )
  )
 ) 
2
  • 1
    you are an absolute legend! Thank you! I couldn't get the first two to work (I had a circular error message) but I'm sure that's most likely down to my own inadequacies than your solution! But the third solution worked liked a charm! Thank you so much for sharing your time and skill in answering my question, it's much appreciated. LEGEND! :D
    – RoCrow
    Mar 22, 2022 at 9:09
  • I'm glad I could help!
    – user290033
    Mar 25, 2022 at 23:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.