Excel Formula to create an organized Data Sheet from an unorganized one.
Hi,
I am practicing and I am just stuck with a problem if we have an unorganized list given to us
(as it is given below).
problem .png
So as you can see the Raw data is from (A1 - O5) and has multiple matching headers from (A1 - O1).
Now I want to create a formula so that all this data is organized inside (P2 - T5) based on their headers (P1 - T1) as below:
solution.png
please help me to find the solution
Administrative Note:
Is you forum profile showing the version of Excel that you need this to work for?
Members will tailor the solutions they offer to the version of Office (Excel, NOT Windows) that you have. Please check that your forum profile is up-to-date in this respect. If you aren't sure, in Excel go to File | Account and report what it says below the MS logo at the top of that page. If your version is for Mac, please also state this.
The three most recent versions of Excel are Excel 2019, Excel 2021 and MS365 - if you are using MS365, please give this name along with the release number in your profile (e.g. MS365 Version 2211). This is in the About Excel section further down the Account page.
Thanks.
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.
In P2 copied across and down:
=A2&F2&K2
Thankyou but I want to organize all the data according to the table headers.
Are you still using Excel 2013?
Yes I am still using 2013
are you able to use Power Query (PQ add-in for XL2013) ?
Thanks for your reply. I don't know how to use Power QueryOriginally Posted by sandy666
As per Ali in P2
=A2&F2&K2
copied across 1 column & down. Then in R2, copied across and dnown:
=SUM(C2&H2&M2)
You do not need anything more complicated, if your sample is correct.
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
that's pity
but here you can read something about: https://learn.microsoft.com/en-us/power-query/
done with Power Query
Invoice No Product Name Quantity Rate Amount C100 BB C101 AD C102 GH C103 HG
ThankYou Glenn It worked. Thanks a Lot
Agreed post no #9 but however if you will rearrange you result header then can gi with below formula with CONTROl+SHIFT+ENTER
Formula:
Please Login or Register to view this content.
If I helped, Don't forget to add reputation (click on the little star ★ at bottom of this post)
Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
You're welcome.
It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.
Finally, if that takes care of your original question, please click on "Thread Tools" from the menu link (just above the first post in the thread) and mark this thread as SOLVED.
How come it worked when Glenn suggested it, but was rejected when I suggested it???
Sorry, I can't get your solution on the first hand.
By the way, thank you too AliGW.
The concatenation turned NUMBERS into TEXT.
ThankYOu shukla.ankurAgreed post no #9 but however if you will rearrange you result header then can gi with below formula with CONTROl+SHIFT+ENTER
Formula:Please Login or Register to view this content.
This then:
=IFERROR(--(A2&F2&K2),A2&F2&K2)
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks