Convert a single column of data into a proper table
As stated in the title
I've a Single column with dates and values corresponding to the dates and additonal values attached. how do I convert it into a proper table?
Note that the dates are in text format.
I've been unable to do so using formulas manually copy and pasting as I was running out of time to submit the work.
Things I've tried so far: filter, vlookup and trying to find a way to convert the text dates to proper dates etc.
see attached for a better understanding.
Thanks in advance.
Why did you post in the Access section? Is this an Excel query, and if not, please elaborate.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
sorry I thought this was the right place to post . since its about data... could you move it to the right section for me? or do I need to repost it somewhere else? thanks
Last edited by AliGW; 07-12-2021 at 10:56 AM. Reason: PLEASE don't quote unnecessarily!
I've moved it into the Excel section for you. No need for you to do anything.
anyone have any input? thanks in advance
Why are there two differences under the same date? And repeat Pls look at Red rectangular box and blue
It takes 3 formulas to complete with formulas, which is more complicated, and VBA can be done with one click.
Last edited by wk9128; 07-15-2021 at 09:21 AM.
What is date format. dd/mm/yyyy or mm/dd/yyyy?
Also show example results manually calculated in the file.
Last edited by kvsrinivasamurthy; 07-15-2021 at 03:42 AM.
Pl note
Array formula should be confirmed with Ctrl+Shift+Enter keys together.
If answere is satisfactory press * to add reputation.
Hi it can be done with Power Query:
HTML Code:
Diana Tanase
If the solutions offered helped you to solve your problem, then mark the thread as SOLVED (thread tools in the top menu) and you can click on * to add reputation to those who helped you, as a way to say thank you !
This is just a recreated random generated sample of my actual data which I cannot post here since it contains sensitive information.Originally Posted by wk9128
B2 value is the sum of B3:B9
Ignore the duplicates. they're not significant
TIP: Don't provide sample data containing duplicates when the real data contains none. YES, it makes a difference!!!
thanks for the tip...
Thank you very much tanasedn. This works beautifully.
Last edited by joeltan; 07-15-2021 at 07:10 AM.
@joeltan , Please see my #6 attachment file , is the animation files the result you want?
It is not rare to find your secret information, but to figure out some abnormal situations should not occur, for example, there is data in column B after the date, and where should it be classified? No matter it is converted with third-party software, there will be no duplication and double The size of the data is different. You have to thank me for finding out all kinds of problems that shouldn't arise, and reviewing your third-party software problems
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks