Sanjay Joshi
07-25-2018, 01:53 PM
Hi,
I have an excel file with column names in first row. Let say there are around 30 columns to count including text value and numeric values in different rows. Now to count the non blank cells/rows within these columns, I can use CountA function however the catch here is, some columns' name is similar like ColumnA_1,ColumnA_2,ColumnA_3, ColumnB_1,CoulmnB_2,ColumnB_3 etc.
So the requirement is, if there is value in any of the cell within these similar columns, it should be counted as 1.For example if in first row, ColumnA_1 and ColumnA_2 and ColumnA_3 have values, it should be counted as 1 only. Similarly if any row has no data for ColumnB_1 but has data for ColumnB_2 and ColumnB_3, it should also be counted as 1. Rest of the variables should be counted accordingly.
The output I'm looking is something like below (output at column J).
A
B
C
D
E
F
G
H
I
J
K
ColumnA_1
CoulmnA_2
CoulmnA_3
CoulmnB_1
CoulmnB_2
CoulmnB_3
CoulmnC
CoulmnD
Expected Counts
Notes
1
1
1
1
1
1,2,3
text
4
count columns A,D,G,H
5
3
6
6,2
name
4
count columns B,F,G,H
2
8
1
17
9
4,9,11,7
3
count columns A,D,G
Above table is attached as well.
Can you provide any macro for above calculations please.
Many Thanks,
Sanjay
I have an excel file with column names in first row. Let say there are around 30 columns to count including text value and numeric values in different rows. Now to count the non blank cells/rows within these columns, I can use CountA function however the catch here is, some columns' name is similar like ColumnA_1,ColumnA_2,ColumnA_3, ColumnB_1,CoulmnB_2,ColumnB_3 etc.
So the requirement is, if there is value in any of the cell within these similar columns, it should be counted as 1.For example if in first row, ColumnA_1 and ColumnA_2 and ColumnA_3 have values, it should be counted as 1 only. Similarly if any row has no data for ColumnB_1 but has data for ColumnB_2 and ColumnB_3, it should also be counted as 1. Rest of the variables should be counted accordingly.
The output I'm looking is something like below (output at column J).
A
B
C
D
E
F
G
H
I
J
K
ColumnA_1
CoulmnA_2
CoulmnA_3
CoulmnB_1
CoulmnB_2
CoulmnB_3
CoulmnC
CoulmnD
Expected Counts
Notes
1
1
1
1
1
1,2,3
text
4
count columns A,D,G,H
5
3
6
6,2
name
4
count columns B,F,G,H
2
8
1
17
9
4,9,11,7
3
count columns A,D,G
Above table is attached as well.
Can you provide any macro for above calculations please.
Many Thanks,
Sanjay