2

Much like the problem with the transposing of data in transpose column data I am stuck trying to transpose a set of data with multiple variables. The biggest issue I face is trying to remove useless data. Table 1 is how the data is received

Column N
Sep 07 2022
Alert
Something went wrong
fish company
70000123456
1234567
231.03
View Details
Sep 07 2022
---
meat company
70000987654
688773
View Details
Sep 07 2022
Success
produce company
70000192837
View Details

Table 2 is the desired output

Column A Column B Column C Column D Column E
date vendor po Invoice cost
Sep 07 2022 fish company 70000123456 1234567 231.03
Sep 08 2022 meat company 70000987654 D688773B
Sep 07 2022 produce company 70000192837

I was unable to trim cells Alert and Something went wrong due to nesting errors.

TheMaster
  • 45,448
  • 6
  • 62
  • 85
Nick m
  • 35
  • 6
  • [Edit] to confirm what factors would be used to determine the attribute. Eg: Rows destined for column B will always have "company" at the end, Rows for column C will always start with 7 and have 10 digits, etc. – TheMaster Oct 25 '22 at 20:36
  • Column A will always b MMM DD YYYY, Column B will be known vendors so a reference list is possible to use. Column C will always be 7 followed by 4 zeros, Column D and E can be missing as shown. groups are bookended by Date then "View Details". – Nick m Oct 25 '22 at 21:39

2 Answers2

4

REDUCE the array to the string, joined by delimiters. If the value is a date, join by , else if it's a value of interest determined by REGEXMATCH, join by . From the created string, split by the row delimiter , TRANSPOSE and SPLIT by the column delimiter 

=ARRAYFORMULA(SPLIT(TRANSPOSE(SPLIT(REDUCE(,A2:A20,LAMBDA(a,c,IFS(ISDATE(c),a&""&TO_TEXT(c),REGEXMATCH(TO_TEXT(c),".*company|70{5}\d+|\d+"),a&""&c,TRUE,a))),"")),""))
Sep 07 2022 fish company 70000123456 1234567 231.03
Sep 07 2022 meat company 70000987654 688773
Sep 07 2022 produce company 70000192837
TheMaster
  • 45,448
  • 6
  • 62
  • 85
  • Thank you so much for the work you have done so far. The column B with company names are placeholders for names i did not want to disclose. Is there a way to change that? – Nick m Oct 26 '22 at 01:39
  • 1
    @Nickm This is a sample. You're expected to do the rest of work by modifying things as necessary. Try `XMATCH+ISNUMBER`. If you're unable to figure it out after reasonable research(at least a few hours), ask a new question – TheMaster Oct 26 '22 at 05:23
  • 1
    @Nickm I agree with TheMaster... it wasn't part of your question and you should accept this answer as it's pretty impressive. I found like three new formulas I didn't know about. – pgSystemTester Oct 26 '22 at 20:26
  • 1
    @pgSystemTester What were the three? – TheMaster Oct 26 '22 at 20:30
  • 1
    @TheMaster REDUCE, COMPANY and – pgSystemTester Oct 26 '22 at 20:40
  • 1
    Just kidding. `To_Text` and `Reduce` (I can't count). – pgSystemTester Oct 26 '22 at 20:40
  • 1
    @pgSystemTester I know you were just kidding :) But Since you seem to be active in [excel], I'm surprised you weren't aware of ``reduce``, which landed in excel at least a year back, I believe. – TheMaster Oct 26 '22 at 20:58
  • 1
    I may have heard of it, but definitely not `TO_TEXT` . I'm still mesmerized by spill ranges from 2019; i only heard about some new functions a few months ago, mostly related to arrays, which is much needed! I still don't get what you did with `reduce` combined with `lambda`. I know what reduce means in javaScript so I'm sure I can figure it out, but in my 5 minutes of staring at your formula, the only thing I came up with was comedic material relating to your choice of string splitters (which was quite pragmatic). – pgSystemTester Oct 26 '22 at 21:20
  • 1
    @pgSystemTester I think you didn't know the syntax. But the concept is the same as Javascript. `a` is accumulator and `c` is current value. Basically it's a simple loop. – TheMaster Oct 26 '22 at 21:29
  • @TheMaster Hey, do you think there is a way to do this using the new WRAPROWS or WRAPCOLS functions. Been trying to use them with FILTER and REGEXMATCH to no avail. Would love to know if there is any use in this case. – Nick m Apr 04 '23 at 21:34
  • 1
    @Nickm Highly doubt it. `REDUCE` provides a unique feature of iterating per value. – TheMaster Apr 05 '23 at 03:20
  • @TheMaster Bummer about that, thank you for letting me know. – Nick m Apr 05 '23 at 13:35
  • @TheMaster Hey so i practiced and read up on regex and learned that regex could be used to pick out keywords that i do not want and found that this formula =ARRAYFORMULA(SPLIT(TRANSPOSE(SPLIT(REDUCE(,P:P,LAMBDA(a,c,IFS(ISDATE(c),a&""&TO_TEXT(c),REGEXMATCH(TO_TEXT(c),"[^((?!((^|, )(View Details|Alert|Something went wrong|Success|Error|^<>%---$))+$).)*$]"),a&""&c,TRUE,a))),"")),"")) works how i would want it too. doing the same as how you built it but removing key phrases. – Nick m Apr 12 '23 at 19:55
3

If you don't care about dragging formulas, you might be able to use something like the following steps I did:

  1. Pasted your data starting in cell A2.
  2. Put a formula for to identify dates to the right of your data starting in cell B2: =N(B1)+if(ISDATE(A2),1,0) (NOTE this formula isn't dynamic)
  3. Create a unique list filter list cell D1: =UNIQUE(Filter(B:B,B:B<>""))
  4. Used formula to parse out data next to unique list (so starting in E2): =Transpose(FILTER(if(A:A="Alert",,A:A),(B:B=D2)*(A:A<>"ALert")*(A:A<>"Something Went Wrong")*(A:A<>"View Details")))

As you can see in part 4, I tried to strip out members that you flagged as irrelevant. I'm not sure what other rules you have.

There's probably a way to make steps 2 and 4 dynamic spill formulas, but that's all I have time for.

Ended up with this (yellow cells have relevant formula). enter image description here

pgSystemTester
  • 8,979
  • 2
  • 23
  • 49
  • thank you very much for the work you put in. If it wasnt for the need to discard text this would be a good choice. Thank you – Nick m Oct 26 '22 at 01:42