Excel table with information

Merlini · Jan 25, 2019

Hi all,

Hope You can help me.
I have a pretty big table and want some information out of it but don't know how to do it. Hope You guys (and girls) have some ideas.

The idea of the table is:

	A	B	C	D	E	F	G	H	I	J
Name 1	V	V	V	V	V	V	V	V	V	V
Name 2							V	V
Name 3		V	V	V	V
Name 4	K	K	K	K	K	K	K
Name 5	V	V						K	K	K

I want to count how many times the combination of person and column has a specific letter.
For example:

	Times V
Name 1+2	2
Name 1+3	4
Name 1+4	0
Name 1+5	2
Name 2+3	0
Name 2+4	0
Name 2+5	0
Name 3+4	0
Name 3+5	1
Name 4+5	0

And if it's possible the combination of three names.
Is there someone who has an idea?

Thanks in advance!

Foz1980 · Jan 25, 2019

Have you tried using a pivot table?

AlanY · Jan 25, 2019

try this

Book1

A

B

C

D

E

F

G

H

I

J

K

1

Name 1

V

2

Name 2

V

3

Name 3

V

4

Name 4

K

5

Name 5

V

K

6

7

8

9

10

V

K

11

Name 1

Name 2

Name 3

15

0

12

Name 1

Name 3

Name 4

13

7

13

Name 1

Name 4

Name 5

11

10

14

Name 1

Name 5

Name 3

15

3

15

Name 2

Name 3

Name 4

6

7

16

Name 2

Name 4

Name 5

4

10

17

Name 2

Name 5

Name 4

4

10

18

Name 3

Name 4

Name 5

6

10

19

Name 3

Name 5

8

6

20

Name 4

Name 5

Name 2

4

10

Sheet1

Cell Formulas
Range	Formula
D11	=SUM(SUMPRODUCT(($A11=$A$1:$A$5)($B$1:$K$5=D$10)))+SUM(SUMPRODUCT(($B11=$A$1:$A$5)($B$1:$K$5=D$10)))+SUM(SUMPRODUCT(($C11=$A$1:$A$5)*($B$1:$K$5=D$10)))

Merlini · Jan 25, 2019

Tnx for helping.
I tried. But don't know how to filter on 2 same letters in the same column. Or do You think it's possible with a Pivot table?

Merlini · Jan 25, 2019

Tnx for the reply. Just looked how your formula works but it doesn't right do what I want.
If for 3 names in your example, only column C is counted, because there is a "V" in all off them.

But your formula brings some new ideas to work with.

AlanY · Jan 25, 2019

Merlini said:
Tnx for the reply. Just looked how your formula works but it doesn't right do what I want.
If for 3 names in your example, only column C is counted, because there is a "V" in all off them.

But your formula brings some new ideas to work with.

not quite sure what you meant?
give an example which cell's answer is wrong so that we can have a look

TotallyConfused · Jan 25, 2019

Originally Posted by Merlini

Tnx for the reply. Just looked how your formula works but it doesn't right do what I want.
If for 3 names in your example, only column C is counted, because there is a "V" in all off them.

But your formula brings some new ideas to work with.

AlanY said:
not quite sure what you meant?
give an example which cell's answer is wrong so that we can have a look

Alan, your formula is a good one and does what it is intended to do, however, I don’t think that is quite what the original poster is asking. I’m referring back to posting #1 , where it is stated that, “I want to count how many times the COMBINATION of person AND column has a specific letter.” The question is how many times this happens, and not how many letters are involved. The key words being ‘COMBINATION’ and also ‘AND’. In other words, the same letter must appear in the same column in two or more rows. What your formula does is count the total number of times a letter appears in the rows or columns being considered. Referring now to the desired result table in posting #1 , you’ll see that it’s stated the letter ‘V’ appears 2 times for Name 1+2, both times in column ‘G’ and “H’. If comparing three names, then the only time a letter will appear in the same column would be in column ‘B’ for Name1, Name3 and Name5.

I think it is safe to say that the answer can never be more than the minimum number of times a letter appears in any one row that is being included in the count.

I’m not familiar with Pivot tables as another poster mentioned, but I’m wondering if this problem can be solved within Excel, or does the answer lie in using VBA?

TotallyConfused

Merlini · Jan 25, 2019

Its correct what totallyconfused says. Iam looking also for the combination of character, name and column. And the count of the combination of that 3. I have some knowledge of VBA but i cant solve the problem wijt that either.

Nog thinking of the idea of giving all the characters an Unique prime number and multiple them for all the name possibilities.

Then i can count the values of the outcome.
But it Will be monday because i lack time this weekend.

Thanks for all the support!

Excel table with information

Merlini

New Member

Excel Facts

Foz1980

Board Regular

AlanY

Well-known Member

Merlini

New Member

Merlini

New Member

AlanY

Well-known Member

TotallyConfused

Board Regular

Merlini

New Member

Similar threads

Forum statistics

Share this page

Excel table with information

New Member

Excel Facts

Board Regular

Well-known Member

New Member

New Member

Well-known Member

Board Regular

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock