CODEWITHSUNDEEP
excelfilterstatisticsgrouping
Ousef Kuruvilla
Ousef Kuruvilla

Reputation: 23

How do you group data in columns?

I have numeric data under fifty samples that are mostly similar. I want to count identical columns and give statistics on the same. There are too many rows to select them (37,888). Data looks like: 

Sample 1 Sample 2 Sample 3     ........ Sample 50
4          4         0
4          4         0
4          4        ...
0          0
0          0
0          0
0          0
...        ...
upto thousands of rows for each sample.

There is a column for date/time as well, would be nice if I could include that in the grouping.

In this snippet, there are many rows. Sample 1 and 2 are identical hence should be grouped together. Sample three would form another group and so on.

Upvotes: 1

Views: 61

Answers (1)

Danny
Danny

Reputation: 460

While I'm not sure what "There are too many rows to select them" means in this context (there is no limit on the number of rows or items that can be selected and included in a formula), this looks like a job for array formulas.

If you want to determine (for instance) whether columns C and D are equal, from rows 1 through 37888, you can use this formula:

=AND(C1:C37888=D1:D37888)

To make Excel treat this as an array formula, you need to press CTRL-SHIFT-ENTER (Windows) or CMD-ENTER (Mac) after typing the formula. The "AND" function will return TRUE if and only if all corresponding entries are equal: C1=D1, C2=D2, C3=D3, ..., C37888=D37888. It returns FALSE if any corresponding entries disagree.

Exactly what you do next will depend on the nature of the statistics that you want to compute for each group, but this formula will at least help you figure out which columns belong in the same group together.

Upvotes: 1

Related Questions