Forum Discussion
Sorting imported rows
I have Teams data for members in our organisation. The issue with this data is if a user has no data for that day, they have no entry, which means the order is different each day.
I need to import this data into an existing excel spreadsheet with all the rows in an exact order (by date then by name alphabetically).
How would I go about either writing a formula or using power query to take the rows I copy and paste into the spreadsheet and spit them out in the correct order, either adding 0 into each missing row or leaving blank?
3 Replies
IMHO, with Power Query it'll be easier. First you need to generate separate calendar table. If only dates, you may use List.Dates() function, for more complex cases there are a lot of samples how to generate Calendar. Merge it with your data on dates and expand values. Nulls will be for dates without data. Do cosmetic and return result into the Excel sheet.
An interesting question. I wonder, first, if it might be possible for you to post a copy (or a facsimile) of your actual spreadsheet and the data you need to import. Just don't have real names or any confidential information.
But if we were meeting face-to-face, I'd want to step back from your "presenting problem" to ask more about what you're doing with the data? What's the "output" expected from the data you're collecting on each team member on a daily basis (that being the "input')?
Excel does a good job, in a number of ways, taking raw data--including data that may have missing rows on any given day for any given team member--and summarizing that data. In general it's a mistake to try to do at the Input end of things what should be saved for the Output end.
So I would want, in that face-to-face meeting, to first look at the data being collected and not worry too much about "blank days." When there's no data, there's no data. That can be dealt with at the Output stage. So let's assume we have a simpler database that just consists of rows of data for each team member for each date that team member has data. No row at all if they don't.
Now let's look at some options for Output (much of which would depend on more knowledge of how extensive the data is on each person each day).
- You could use the Pivot Table to produce a summary by team member, by date, of whatever it is that you're counting/tracking. If, for example, it's a relatively simple matter of, say, numbers of sales, number of calls, dollar value of sales...any one or two of things like that...the Pivot Table could automatically produce a very neat summary report; and blank days would be blank days.
- Or you could use SORT and UNIQUE to extract a list of all team members, and then FILTER to produce the data that is associated with each team member for a designated set of dates. And that could be done on a "dashboard" that extracts the data from the database. This option would be feasible if the stuff you're tracking is more extensive.
So let's assume we're sitting down and talking face-to-face. Give me (as well as others here) a more complete picture of the data you're tracking and what kind of reporting or analysis you want to do. That will help us help you.
With your permission, if I can recommend. It can help us all if you upload an Excel file (without sensitive data), no picture. Even if it is said that a picture can say a thousand words, it is certainly not in the case of Excel, on the contrary in some cases. You could get a precise solution much faster with a file (w/out sensitive data). This would also be a blessing for all of us, as we can understand the problem much better, a win-win situation for everyone.
* Knowing the Excel version and operating system would also be an advantage.
Thank you for your understanding and patience
Nikolino
I know I don't know anything (Socrates)
