Hi Friend,
I need your help to transpose data from Row to Column based on date.
Please refer attached for further understanding
Hi Friend,
I need your help to transpose data from Row to Column based on date.
Please refer attached for further understanding
Hi neo4ride and welcome to the forum,
I'd create two helper columns first with your data. Then a Pivot Table will do the trick. See the attached where I've done both and shown your desired results.
One test is worth a thousand opinions.
Click the * Add Reputation below to say thanks.
Marvin, can you please tell me how to get ID Number 20605 from A16:A19, When I tried pivot table in Cell A15 only showing 20605 and A16:A19 shows as blank
Samba
Say thanks to those who have helped you by clicking Add Reputation star.
Thanks Marvin....
Is there any other quicker way?
I'm not sure what you mean by "quciker", but htis will work. It also uses 2 helper columns, one on the source sheet and one on the target sheet. Some of the formulae are array formula. Array Formulae are a little different from ordinary formulas in that they MUST be confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.
You will know the array is active when you see curly braces { } appear around the outside of your formula. If you do not CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.
Don't type the curly braces yourself - it won't work...
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
Thanks Glen & Marvin... Both way help me a lot...
Marvin way is pretty quick. It just for pivotable I need to fill up empty colum (ID and Name) manually.
Glen way also quick. It just, its seem too advance for me. But I try to learn it.
Thanks for both of you
'Hi nflsales,
You are asking one of two questions.
1. How do you filter to only show 20605 in the pivot table?
Answer: Click on the dropdown box in cell A14 and uncheck the other box (20211).
2. How do you repeat items in a pivot table and make the row items in different columns?
Answer: Click anywhere in the Pivot Table and then look at the very top of the window at "Pivot Tools". Just underneath this word is Analize/Design. Click on Design. A new Icon Ribbon will appear and you want to click on "Report Layout". Play with these 5 different formats to see what they do. Repeating all item labels in Outline (or tabular) Form is what I think you are asking to find.
I hope one of the two questions/answers above answers your question.
BTW - I think I also clicked on "Grand Totals" in the Pivot Table Tools -> Design ribbon and didn't show Row or Column Grand Totals in this problem. This was to make it look exactly like the OP wanted.
There might be a "quicker" way that would involve Small and/or Array Formulas. I'd much rather do this problem with helper columns.
Some other guru might be able to leave out Column E (Helper1 column) and go right to column F. I just don't remember that construct.
If you create a Dynamic Named Range for your data on the Current Tab and then base your Pivot Table using that DNR you can grow and shrink the data and the Pivot will reflect more (or less) columns. Then if you put a Workbook.RefreshAll on the event that selects the Desired Tab the Pivot will always reflect changes in the Current Data.
Is that what you meant by a "quicker way"?
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks