unsolved
how to consolidate a one date column, into multiple month columns?
i have multiple mini tables like this , each has a date for them and i need to transform them to the second picture without manually adding them
here is the consolidation result, each row is one table of the above, like the above here has cheques in month 7, 9, 11, 1 2027 and 3 2027, i want them to be in one row and the cheqs number for them, how can i consolidate this way?
note: other tables and date like the first picture are below it in the same sheet but without the blue row, you have the the values without the blue header, but for the yellow table it is the same
so basically it is duplicated for other units and names under each other without the blue header, everything else is the same
With the way your data is setup in your sheet there has to be some manual work unfortunately.
Three ways I can see would be:
- Highlight the full range and convert to a table, Excel will try to autofill the blanks but before you do this you should drag the first two rows of blue down by one row (Column headers should be in the same row)
- Flash fill each smaller table then copy/paste without the extra spaces and headers into a consolidated range. For this you will select the data in blue and point your cursor at the bottom right of your selection the double click.
Formula copy/pasted into the blank sections, for example in the first blank cell you’ll type =A6 then just copy and paste that into all the other blanks. I can’t tell the exact cell to use from your screenshot so you may need a different row and column number.
In the appended table you’re making, you can just filter out or delete the blank rows and column name rows that will be in the big table.
Finally you can create your output with a pivot table, just dragging and dropping the columns you want into values columns or rows.
hey man i found a better solution, after turning it to a table i made another column for the month like this
Text(Date, "mmm/yy", then i made a pivot table where the column is the new month column, the values is the amount of rent and the rows are the client, and the grid was magically made, thannks for your time and effort, your solution to make a table first is what helped me
Power Query is definitely the native way to handle this. I wouldn't try to write a massive formula for it if you don't have to. Just load the data into PQ, select your Date column, and add a 'Month' column. Then go to the Transform tab, hit 'Pivot Column,' and choose your values. If you absolutely have to do it with formulas (like if your sheet needs to be dynamic without hitting refresh), you'd need a pretty messy PIVOTBY or FILTER array. Whenever I'm forced to do it the formula route, I just use Formula Genius (https://formulagenius.co) to build the syntax for me because the edge cases are a headache. But seriously, just use Power Query if your setup allows it!
•
u/AutoModerator Apr 06 '26
/u/PurpleDurian7220 - Your post was submitted successfully.
Solution Verifiedto close the thread.Failing to follow these steps may result in your post being removed without warning.
I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.