Sorting information in Excel.

[Seriouz-Bizniz]

Hello,

I have some cellular data in an Excel spreadsheet that I would like to have categorized into three different columns. I would like to have the data categorized into the following: Operating System (Android, iPhone,) Browser (Firefox, Safari) and Version of the browser for each of the devices.

The spreadsheet contains about 10k + columns and I don't want to imagine the level of effort it would take for me to manually sort through this information.

Is there a formula in Excel that would make this process more seamless? I tried google, but I wasn't able to find the exact information that I was looking for.

Any help would be greatly appreciated!

 

[Ichinisan]

Sorting is super easy. Just find the "Sort" option in the toolbar or menu bar.

If the first row of your columns is a header row ("OS, Browser, Browser Ver"), check the box to indicate that.

Select which column you're sorting by, then click to sort!

 

[pete6032]

You could either use sort, or try a pivot table.

 

[sbpromania]

This article from Microsoft could help: https://support.office.com/en-in/ar...or-table-62d0b95d-2a90-4610-a6ae-2e545c4a4654

 

[CluelessOne]

Did you mean a 10K+ row data per column or a 10K+ column in the workbook? If the first scenario, it's easiest to use sort. If the second scenario, you would need to consolidate data to a temp summary worksheet before doing the sorting. That's a lot of column and worksheets though.

 

[Binky]

OP - define "sort."

Do you mean simple sorting, like arranging things in order from small to large? Or do you mean categorizing and summarizing?

The first one is easy, but I suspect that's not what you're asking. The second, sorting/summarizing can be done in many ways. Check out pivot tables, and the sumifs and sumproduct functions.

 

[Seriouz-Bizniz]

Did you mean a 10K+ row data per column or a 10K+ column in the workbook? If the first scenario, it's easiest to use sort. If the second scenario, you would need to consolidate data to a temp summary worksheet before doing the sorting. That's a lot of column and worksheets though.

10k Column in the entire workbook. The entire spreadsheet has exactly 10212 columns. I have data in row D that needs to be broken into three columns, but doing this manually would require a considerable amount of time especially for 10,212 columns. I will sort the information from that column to put into a pivot table for presentation purposes.

 

[CluelessOne]

Considering that there is only 230 columns per sheet, that is a LOT of sheets in the workbook for 10K+ columns, over 40 sheets in the workbook.

If you have only a few rows of data for each column, I would transpose the data in a new consolidation sheet so that the columns become rows and rows become column, and copy paste so all the data is in one sheet, which would become your pivot table source. You could also use the worksheet function TRANSPOSE to do this, but copy paste is also good.

You could look at Excel MVPs webpages to get better tips. Starting with Debra Dalgleish (lots of Pivot Table tips) or Chip Pearson for Excel VBAs.

 

[Binky]

You can have 16,384 columns per sheet in excel 2010. Not sure about the newer 365 version(s).

Be sure to save large workbooks like that as a binary file (XLSB). They work a bit faster in that format and the file size is smaller.

 

[CluelessOne]

I only have a lowly Office 2003, so I don't know that. If the data is all in one sheet then it is very easy to sort the required data.

Go to the first empty row on first column.
Type = if ( A4=condition1;column();"") -> you get the column number that satisfies condition 1, replace A4 with the actual reference.
Copy across range of column.
Repeat for condition 2 and condition 3.

On a new sheet, put cursor at top leftmost cell
put formula = index(DataReferenceRange;row();small(RowOfCondition1Result;columns($1:1)))
Copy formula throughout the range of column for all data (all 10K+ of it).
Copy formula throughout the range of rows for the data (last 3 calculated row not included).

Do this for the other two conditions on other sheets.
You have all the data for presentation ready.
Put DataReference as your data range minus the 3 rows calculated previously.