0

I've got a large set of data that looks like:

this

and I've been trying to format it so that instead of having a column of dates for each item, there's only one column of dates on the left. The problem is some of them have missing dates and I don't know how to insert a blank cell for those dates as well as make all the data match the correct date.

Improve this question
1
  • I would suggest developing a VBA or Power Query solution. If you have O365, a complex formula might be possible.
    – Ron Rosenfeld
    Commented Jan 22, 2021 at 12:25

1 Answer 1

Reset to default
0

If you do NOT have O365, I suggest a VBA or Power Query solution.

However, if you have Excel O365, you can try the following:

  • Create a Table from your data (my Table name is Table5; adjust the formulas to match your table name.
    • Doing so will automatically place Headers in the blank columns with names like ColumnN where N is a number

Given that my results start in A22 (see below)

Formulas:

  • A sorted list of the unique dates from your original table:

      A23: =SORT(UNIQUE(FILTERXML("<t><s>" & TEXTJOIN("</s><s>",TRUE,INDEX(Table5,SEQUENCE(ROWS(Table5)),SEQUENCE(,COLUMNS(Table5)/2,1,2))) & "</s></t>","//s")))

  • A list of the Corporations:

      B22: =FILTER(Table5[#Headers],ISERR(FIND("Column",Table5[#Headers])))

  • Return the matching values

      B23: =LET(x,INDEX(Table5,SEQUENCE(ROWS(Table5)),SEQUENCE(,2,MATCH(B$22,Table5[#Headers],0)-1)),y,XLOOKUP($A23,INDEX(x,0,1),INDEX(x,0,2),""),y)

Select B23 and fill down and across to fill the results table.

enter image description here

Explanation of Unique Dates formula:

INDEX(Table5,SEQUENCE(ROWS(Table5)),SEQUENCE(,COLUMNS(Table5)/2,1,2))

will return a table that contains only the odd numbered columns (which are the date columns)

TEXTJOIN("</s><s>",TRUE,the_odd_column_table)

joins all of the entries in those date columns into a single string with the specified delimiter

FILTERXML("<t><s>" & the_joined_string & "</s></t>","//s")))

creates a XML and then returns an array of the nodes (which will be the dates from the original table).

SORT(UNIQUE(the_dates_array))

does the obvious.

Improve this answer
8
  • Thanks for having a look, the formula for B22 worked perfectly but not the one for A23. I made sure that the table was also called table5 but all it gave was #VALUE!. Since B23 references A23, I assume that was also why that cell didn't work and only gave #VALUE! as well. I just installed O365 so I'm not sure if there are different versions of it that may not support the formula.
    – froghurtbeaver
    Commented Jan 22, 2021 at 21:09
  • Do you have a Mac? Or are you running on Windows?
    – Ron Rosenfeld
    Commented Jan 23, 2021 at 0:09
  • I'm running on Windows. Would it help if I tried it on mac? If it helps, I can manually enter the dates since I know when the earliest and last one is, although I don't know how to adapt the formula to use those dates instead of the generated one.
    – froghurtbeaver
    Commented Jan 23, 2021 at 0:12
  • @froghurtbeaver No, I wouldn't expect it to run on a Mac. How many rows and columns in your data?
    – Ron Rosenfeld
    Commented Jan 23, 2021 at 0:12
  • It goes from A-MT, 1-771
    – froghurtbeaver
    Commented Jan 23, 2021 at 0:13

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .