Calc Sum of identical columns

gregguu · June 21, 2020, 8:00am

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?

keme · June 21, 2020, 9:00am

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.

gregguu · June 21, 2020, 9:55am

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

Lupp · June 21, 2020, 8:55am

“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

gregguu · June 21, 2020, 9:57am

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

Lupp · June 21, 2020, 12:50pm

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.

Lupp · June 21, 2020, 2:24pm

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

LeroyG · June 21, 2020, 4:42pm

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

mikekaganski · June 21, 2020, 4:51pm

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.