extract information

[MikBas]

Hello

Please could you help me extracting information from a column.

Normally I would use filter but Im trying to make an automated spreasheet (so that I only paste the data to column A and the formula automatically comes back with the list in column C)

is this possible? workbook attached

Thanks!

example 

[#2]

Hello @MikBas 

in C1:

=LET(
  noBlank, FILTER(A:A, A:A<>""),
  INDEX(noBlank, SEQUENCE(ROUNDUP((ROWS(noBlank)-2)/2,0),,3,2))
)

[#3]

OK but now if I add another 'Order placed on PO4' onto cell A8 it will still only show the 3 results.

In essence I need a list of all 'Order placed on PO...' in column C - but it will never be the same amount.

The future raw data will be different - is there a way to tackle that?

[#4]

@MikBas 

Glad this help & Thanks for providing feedback

Just in case your 2nd row where you wrote "blank" is actually blank/empty:

=LET(
  noBlank, FILTER(A:A, A:A<>""),
  INDEX(noBlank, SEQUENCE(ROUNDUP((ROWS(noBlank)-1)/2,0),,2,2))
)

[#5]

OK but now if I add another 'Order placed on PO4' onto cell A8 it will still only show the 3 results.



In essence I need a list of all 'Order placed on PO...' in column C - but it will never be the same amount/value. One day there might be 3 lines, another 30 lines with all different PO numbers at the end. But the beginning phrase will always be 'Order placed on PO'.



The future raw data will be different - is there a way to tackle that?

[#6]

@MikBas 

OK but now if I add another 'Order placed on PO4' onto cell A8 it will still only show the 3 results

I worked under the assumption - given what you posted - that there's always "something" between each PO row. So, if in A8 you have "blabla" and "Order placed on PO4" in A9 you should get what you want

[#7]

@MikBas 

In essence I need a list of all 'Order placed on PO...' in column C - but it will never be the same amount

Is "Order placed..." a consistent string?

[#8]

Sorry the example was simplified - it will never be the same 'something' in between.

Potentially 100 non-important lines, then 'Order placed on PO123456', then another 100 lines of something , followed by 'Order placed on PO654321'.

So I only would need those two 'Order placed on PO...' lines on my list.

[#9]

yes, 'Order placed on PO...' is consistent string.

[#10 · Accepted solution]

@MikBas 

In essence I need a list of all 'Order placed on PO...' in column C - but it will never be the same amount/value. One day there might be 3 lines, another 30 lines with all different PO numbers at the end. But the beginning phrase will always be 'Order placed on PO'.

OK, so the following should do it:

=LET(
  noBlank, FILTER(A:A, A:A<>""),
  FILTER(noBlank, LEFT(noBlank,15)="order placed on")
)

[#11]

Ah that worked - Amazing! Thank You!

[#12]

Glad this helped. Nice EOD...