06-04-2020 12:57 AM
06-04-2020 02:36 AM
@adeliasuharly I suspect that your example may be a simplified representation of your real data, but perhaps the attached example can help you work out a solution that works for you. Note, I introduced two helper columns to aid in making a 9x1 range out of the 3x3 range.
06-30-2020 10:00 PM
@adeliasuharly I suspected that. But how complex is it? Excel can handle huge amounts of data and solve pretty complex problems. Would be helpful if you could provide a more realistic example of your data (exclude private and confidential information) and indicate which Excel version you are working with and on what platform (Windows, Mac, Web).
07-01-2020 06:09 AM - edited 07-01-2020 06:29 AMSolution
@adeliasuharly Would you be willing to learn? It's not very difficult for the kind of job you need it to do. Attached a PowerQuery example, just to demonstrate that it's possible. But, difficult to offer a solution to your specific situation, based on a picture alone.
The blue table could represent your data and the green one is the PQ result.
07-01-2020 08:24 AM
woww Thank youu,your example is what i want.but ive tried 7 times to import my file into query feature but it doesnt work. actually i could do my work manually.but it takes a lot of time.meanwhile i need it very quick.your tutorial make my life easier.but i think i still need more time to learn...@Riny_van_Eekelen.
so this are my data in excel. i want to change the tabel format in " COMPLETE DATA" just like in " TESTING FILE".
07-01-2020 11:03 AM
@adeliasuharly Well, I started by selecting R1:V164. Then Data, From Table/Range.
Confirm "my table has headers".
Then the PQ Editor opens and it looks like this:
Select the first column, press Shift and the last column. All columns turn green.
Then, right-click in any of the column headers and select "Unpivot columns".
Then, right-click the Attribute column and "Remove column"
Now you will be left with a list of values as in the example file.
Save your changes via "Close & Load to"
... a Table and into a new Worksheet.
This should create your list as in the example file I uploaded earlier.
Now, having said all of this I realise that this is not at all straight-forward if you have never used this feature before. But it is not as difficult as it seems. Try and start over if you don't succeed. Don't give up!
Most of what I know today about PQ, I have learned here a couple of weeks ago.