Forum Discussion

MikBas's avatar
MikBas
Copper Contributor
Apr 25, 2023
Solved

extract information

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 

excel
  • Lorenzo's avatar
    Lorenzo
    Apr 25, 2023

    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 Replies

Sort By
  • Lorenzo's avatar
    Lorenzo
    Silver Contributor
    Apr 25, 2023

    Hello MikBas 

     

    in C1:

    =LET(
  noBlank, FILTER(A:A, A:A<>""),
  INDEX(noBlank, SEQUENCE(ROUNDUP((ROWS(noBlank)-2)/2,0),,3,2))
)
    • MikBas's avatar
      MikBas
      Copper Contributor
      Apr 25, 2023

      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?

       

      • Lorenzo's avatar
        Lorenzo
        Silver Contributor
        Apr 25, 2023

        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

Resources