0

I'm trying to format a CSV file that contains a huge list of products (8000+) that are on separate rows but need all the fields to be on the same row. The trouble is that some of the cells are intentionally left blank, so I'd want to stop autofilling once the product ID changed, then resume the autofill once it reached the next product that also had a nonblank cell. I've tried a few things:

=ARRAYFORMULA(VLOOKUP(ROW(W:W),{IF(LEN(V:V),LOOKUP(UNIQUE(V:V),V:V,ROW(V:V))),FILTER({ROW(W:W),W:W},W:W<>"")},2,TRUE))

error but counting the number of changes correctly

=ARRAYFORMULA(IF(LEN(V:V),LOOKUP(UNIQUE(V:V),V:V,ROW(V:V),VLOOKUP(ROW(W:W),FILTER({ROW(W:W),W:W},W:W<>""),2,TRUE))))

error but showing FALSE in the right places =ARRAYFORMULA(IF(LEN(V:V),LOOKUP(UNIQUE(V:V),V:V,VLOOKUP(ROW(W:W),FILTER({ROW(W:W),W:W},W:W<>""),2,TRUE))))

returning some values but in a strange order

Mockup of file: https://docs.google.com/spreadsheets/d/1DAIDk5EphV-RPGzo4Um4k0RZn0A2uhsTiPUApDzafRg/edit?usp=sharing (in this case, it would check when the value of column A changes and stop copying the nonblank cell values of column B)

Any suggestions would be much appreciated!

Improve this question
2
  • Hi and Welcome to WebApps. Interesting problem, and you've described three separate formulas and errors but we have no way to reproduce your results. For starters, would you please edit your question to 1) explain the specific outcome that you are trying to achieve, 2) explain the content of Columns V and W, 3) provide some sample data (either a text table or a sample spreadsheet), 4) define "huge" and 5) supply and describe an example of the output that you are trying to achieve.
    – Tedinoz
    Jan 7, 2022 at 0:49
  • done with a small sample size, should hopefully clarify things - thanks for looking into this!
    – Christian Guest
    Jan 7, 2022 at 14:24

1 Answer 1

Reset to default
1

Put this formula in row 2 of an empty column:

=arrayformula( 
  iferror( 
    vlookup( 
      A2:A, 
      unique(A2:B), 
      column(A2:B) - column(A2) + 1, 
      false 
    ) 
  ) 
)

The formula will grab the first value in column B in each range of column A where a new value appears for the first time.

OUTPUT

snapshot

Improve this answer
4
  • can this be adapted to replicate the first value in column B in each range of column A where the same value appears until a new value appears (see columns G/H vs columns A/B in my sample file)?
    – Christian Guest
    Jan 7, 2022 at 17:43
  • A spreadsheet cell can only contain a value or a formula result. There is no way to write a formula that will skip some cells so that you can fill them manually without disabling or overwriting the formula when you insert values. The easiest solution is to create a modified copy of the source data, using a formula like the one in the answer. There are workarounds, but it is difficult to avoid creating circular dependencies. See The Spreadsheet Value Rule.
    – doubleunary
    Jan 7, 2022 at 19:40
  • @ChristianGuest can this be adapted to... What am I missing here? When I compare "DESIRED OUTPUT FORMAT" to the outcome generated by the answer, I get the exact same result. Did you have something different in mind?
    – Tedinoz
    Jan 8, 2022 at 1:09
  • ah, that's my fault, I was trying to use it on non-adjacent columns
    – Christian Guest
    Jan 9, 2022 at 0:34

Your Answer

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

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