SOLVED

# How to do this

Highlighted
Occasional Contributor

# How to do 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

14 Replies
Highlighted

# Re: How to do this

@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.

Highlighted

# Re: How to do this

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

Highlighted

# Re: How to do this

@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).

Highlighted

# Re: How to do this

so i want to make this blocked data (pict 1) turn into single column (pict 2)

Highlighted

# Re: How to do this

@adeliasuharly That should not be much of a problem with "Get & Transform Data" a.k.a. Power Query. Are you familiar with that?

Highlighted

# Re: How to do this

to be honest, No Sir @Riny_van_Eekelen

Highlighted
Best Response confirmed by adeliasuharly (Occasional Contributor)
Solution

# Re: How to do this

@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.

Highlighted

# Re: How to do this

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".

Highlighted

# Re: How to do this

@adeliasuharly Like in the attached file? Sheet3.

Highlighted

# Re: How to do this

YESS, how to do it? i wish i know how to do it step by step @Riny_van_Eekelen

Highlighted

# Re: How to do this

@adeliasuharly Well, I started by selecting R1:V164. Then Data, From Table/Range.

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.

... 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.

Good luck!

Highlighted

# Re: How to do this

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

Highlighted

# Re: How to do this

@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?

Highlighted

# Re: How to do this

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