Calc Sum of identical columns

English
,
#1

Hi.
I have a problem w with following table (ex.)

prob1	1		1	1	
prob2	1			1	
prob3		1	1		1
prob4					1

I want to count identical columns - 1 and 4 are - on example.
I tried pivot with adding names for columns, but with no success.
Can You help?

#2

That depends on the conditions for equality.

  • Are 1 and empty the only possible states for a cell?
  • Are the empty spaces really empty cells, do they contain “whitespace” text, or do they contain the number zero, but formatted to suppress display of zero?
  • If other numerical values may enter the table, should all nonzero values count as equivalent?
  • Are there only 4 rows, or is the row count variable?

Help us to help you!

With what you have given, we must try to recreate the spreadsheet data from your outline (guessing at the points I mentioned above). If you can edit your question and attach a sample file (use paperclip tool), it will be less work to do for us, and our suggestions for a solution will be based on your actual data structure instead of our “best guess”. This may motivate a few more helpers to make an effort for you.

#3
  • Yes, 1 and empty are only conditions
  • They are empty
  • No, only 1
  • This is an example. I have more tables, which differ on number of rows, but table itself is stable - constant number of rows.

Thanks

#9

Using power of 2 could not allow you to have hundreds of rows. But given the repertoire of the allowed values (empty/1), you may use e.g. =TEXTJOIN(";";0;A1:A100) in a row below the table, and then count duplicates in that row.

#4

“Identical” is a term basically applicable to a pair (of datasequences in this case).
If one sequence is identical with more than one other, you can count the multitude for the specifice sequence, but not for all the sequences as a whole.

Therefore I read your question as “…want to decide which other sequences are identical to any given one…”.
The classes of identical sequences may then also be evaluated for the numbers of their members.

Of course, this cannot be done by a single formula. (Except, probably, resorting to a custom array-function.)
You may want to study the attached example.
ask251515countIdenticalColumnsPerClass_1.ods

#5

In real, this will be more than 100 columns, and sequences will be multiple. I need count on any existing sequence.

#6

OK. That’s what I basically demonstrated, imo.
Based on the assurance of “number 1 or blank” concerning the cell contents it may be slightly simplified.

#7

@gregguu: Did I interpret your question correctly? Did you have a glance on the example?
How long (max) can the sequences be?

#8

@gregguu,

I share an option using row number to the power of two.

Identical columns.ods

EDIT:
Following @mikekaganski idea and some of @erAck ideas there is a new option: Identical columns 2.ods.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.