Jun 04 2020 12:57 AM
Please Help Me,to change this
data 1 data 2 data 3
data 4 data 5 data 6
data 7 data 8 data 9
into this ;
data 1
data 2
data 3
data 4
data 5
data 6
data 7
data 8
data 9
Jun 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.
Jun 30 2020 09:43 PM
woww,thank you so much..but i think my data is too much and too complex . thank ypu so much for your answerr. GBU @Riny_van_Eekelen
Jun 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).
Jul 01 2020 12:13 AM
Jul 01 2020 04:18 AM
@adeliasuharly That should not be much of a problem with "Get & Transform Data" a.k.a. Power Query. Are you familiar with that?
Jul 01 2020 06:09 AM - edited Jul 01 2020 06:29 AM
Solution@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.
Jul 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".
Jul 01 2020 09:05 AM
@adeliasuharly Like in the attached file? Sheet3.
Jul 01 2020 09:19 AM
YESS, how to do it? i wish i know how to do it step by step @Riny_van_Eekelen
Jul 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.
https://www.excelguru.ca/content.php?293-M-is-for-(Data)-Monkey
Good luck!
Jul 02 2020 02:23 AM
thank youu,for your helpp and your support. i've tried the step you told me..but this is what happened. so I have to fix this problem first. but so far it seems like the way you taught me, is the easiest to understand.@Riny_van_Eekelen
Jul 02 2020 02:45 AM
@adeliasuharly How annoying. Such error messages aren't really helpful. Don't have any clue what causes your problem. Does it work OK in the file that I sent earlier?
Jul 02 2020 05:57 AM
Yes, it works for the file you sent earlier. but if you make it from scratch, it becomes an error@Riny_van_Eekelen. but i will find out the solution
Jul 01 2020 06:09 AM - edited Jul 01 2020 06:29 AM
Solution@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.