Turning filtered data into columns

henrycm

New Member
Joined
Aug 12, 2022
Messages
2
Office Version
  1. 2016
Platform
  1. MacOS
So I have a very long column of data - several thousand rows most of which = close to 0. Between every 140 and 170 (not the same gap every time!) rows I have between 9 and 11 data points that I need. If I use a data filter I get the information I need but i have no way to convert that into multiple columns containing those points. I hope that makes sense, please let me know if you have any ideas!
 

Attachments

  • Screenshot 2022-08-12 at 12.45.11.png
    Screenshot 2022-08-12 at 12.45.11.png
    57.7 KB · Views: 14
  • Screenshot 2022-08-12 at 12.39.27.png
    Screenshot 2022-08-12 at 12.39.27.png
    65 KB · Views: 27

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
So I have a very long column of data - several thousand rows most of which = close to 0. Between every 140 and 170 (not the same gap every time!) rows I have between 9 and 11 data points that I need. If I use a data filter I get the information I need but i have no way to convert that into multiple columns containing those points. I hope that makes sense, please let me know if you have any ideas!
Just update your version of Excel here and in your profile - The solution will depend upon that.
 
Upvote 0
Henry
Excel 2016 has many limitations compared to the latest versions. That's why I was asking for the version.

The only solution, I can think about is -
  • If you can sort that list in ascending or descending order - so as to bring all the data you want to convert to columns may come together at one place (I will try to post an example underneath)
  • In case you want to order it back you have to create a fixed index prior or sorting so that you can bring that data back in order it was previously.
  • After sorting in ascending or descending, simply copy (Cmd+C) and paste using (Cmd+Alt+V) keyboard shortcut (THAT's Important).
  • A window shall open - There you can choose Paste Values and transpose simultaneously to get the intended result.
  • After that if you need you can bring that data back to old sorting using the fixed index you created.
  • You can keep or delete that index as per your preference.
I try to give an example below -

All Records.xlsb
ABCDEFGHIJKLM
1Sorted by Fixed IndexSorted by Ascending2172246567194
2Original ListFixed IndexOriginal ListFixed Index
3561214
417621719
526832216
6464464
71585561
811467113
926479415
10200810422
11148910517
12235101146
131611113012
14130121489
1571131585
1621416111
1794151762
18221619123
19105172008
202701822820
21171923510
222282023821
232382124024
24104222647
25191232683
262402427018
Sheet3
 

Attachments

  • Screenshot 2022-08-16 at 23.26.13.png
    Screenshot 2022-08-16 at 23.26.13.png
    66.1 KB · Views: 18
Upvote 0

Forum statistics

Threads
1,223,957
Messages
6,175,622
Members
452,661
Latest member
Nonhle

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top