If multi column has N.X then N.X otherwise keep counting till next N.X

K

Kishan

Well-known Member
Joined
Mar 15, 2011
Messages
1,648
Office Version
  1. 2010
Platform
  1. Windows
Using Excel 2000

Hi,

I have 3 scenario data sets…

1st - data in 2 rows…cells B6:Y7… The Results of these 2 rows in AB6:AY6 for example… if both rows have filled in the same column with N.X then result =N.X…if not count progressively 1, 2, 3, 4…and so on.

2nd - data in 3rows…cells B12:Y14… The Results of these 3 rows in AB12:AY12 for example… if three rows have filled in the same column with N.X then result =N.X…if not count progressively 1, 2, 3, 4…and so on.

3rd - data in 4rows…cells B19:Y22… The Results of these 4 rows in AB19:AY19 for example… if four rows have filled in the same column with N.X then result =N.X…if not count progressively 1, 2, 3, 4…and so on.

Example data sheet is attached below. please need formula or VBA any will do.

NX.xls
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQARASATAUAVAWAXAYAZ
1
2
3
4
5
611N.XN.XN.X1N.XN.X1N.XN.X123N.X1N.X1N.X11 & 212N.XN.X12N.X123456N.X123N.X1
72N.X1N.XN.XN.X1N.X123N.X12N.XN.X1N.XN.XN.X
8
9
10
11
12112N.XN.X12N.XN.XN.XN.XN.XN.X1N.XN.X12N.XN.X1 & 2 & 312N.XN.X12N.X12N.X123N.X123N.X1
1321N.XN.XN.X1N.XN.X1N.XN.X123N.X1N.X1N.X1
143N.X1N.XN.XN.X1N.X12N.XN.X12N.XN.X1N.XN.XN.X
15
16
17
18
1911N.XN.X12N.XN.XN.XN.XN.XN.XN.XN.XN.X12N.XN.XN.X1 & 2 & 3 & 412N.X123N.X12345678910N.X1
20212N.XN.X12N.XN.XN.XN.XN.XN.X1N.XN.X12N.XN.X
2131N.XN.XN.X1N.XN.X1N.XN.X123N.X1N.X1N.X1
224N.X1N.XN.XN.X1N.X123N.X123N.X1N.XN.XN.X
23
24
Hoja11
Cells with Conditional Formatting
CellConditionCell FormatStop If True
AB19:AX19Cell Value="N.X"textYES
AB12:AX12Cell Value="N.X"textYES
AB6:AX6Cell Value="N.X"textYES


Thank you in advance

Regards,
Kishan
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
Sort by date Sort by votes
Try this.

Kishan_4.xlsm
ABCDEFGHIJKLMNOPQRSTUAAABACADAEAFAGAHAIAJAKALAMANAOAPAQARASAT
611N.XN.XN.X1N.XN.X1N.XN.X123N.X1N.X1N.X11 & 212N.XN.X12N.X123456N.X123N.X1
72N.X1N.XN.XN.X1N.X123N.X12N.XN.X1N.XN.XN.X
8
9
10
11
12112N.XN.X12N.XN.XN.XN.XN.XN.X1N.XN.X12N.XN.X1 & 2 & 312N.XN.X12N.X12N.X123N.X123N.X1
1321N.XN.XN.X1N.XN.X1N.XN.X123N.X1N.X1N.X1
143N.X1N.XN.XN.X1N.X12N.XN.X12N.XN.X1N.XN.XN.X
15
16
17
18
1911N.XN.X12N.XN.XN.XN.XN.XN.XN.XN.XN.X12N.XN.XN.X1 & 2 & 3 & 412N.X123N.X12345678910N.X1
20212N.XN.X12N.XN.XN.XN.XN.XN.X1N.XN.X12N.XN.X
2131N.XN.XN.X1N.XN.X1N.XN.X123N.X1N.X1N.X1
224N.X1N.XN.XN.X1N.X123N.X123N.X1N.XN.XN.X
Sheet1
Cell Formulas
RangeFormula
AB6:AT6AB6=IF(COUNTIF(B6:B7,"N.X")=ROWS(B6:B7),B6,N(AA6)+1)
AB12:AT12AB12=IF(COUNTIF(B12:B14,"N.X")=ROWS(B12:B14),B12,N(AA12)+1)
AB19:AT19AB19=IF(COUNTIF(B19:B22,"N.X")=ROWS(B19:B22),B19,N(AA19)+1)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
AB19:AT19Cell Value="N.X"textNO
AB12:AT12Cell Value="N.X"textNO
AB6:AT6Cell Value="N.X"textNO
 
Upvote 1
Solution
Peter_SSs said:
Try this.

Kishan_4.xlsm
ABCDEFGHIJKLMNOPQRSTUAAABACADAEAFAGAHAIAJAKALAMANAOAPAQARASAT
611N.XN.XN.X1N.XN.X1N.XN.X123N.X1N.X1N.X11 & 212N.XN.X12N.X123456N.X123N.X1
72N.X1N.XN.XN.X1N.X123N.X12N.XN.X1N.XN.XN.X
8
9
10
11
12112N.XN.X12N.XN.XN.XN.XN.XN.X1N.XN.X12N.XN.X1 & 2 & 312N.XN.X12N.X12N.X123N.X123N.X1
1321N.XN.XN.X1N.XN.X1N.XN.X123N.X1N.X1N.X1
143N.X1N.XN.XN.X1N.X12N.XN.X12N.XN.X1N.XN.XN.X
15
16
17
18
1911N.XN.X12N.XN.XN.XN.XN.XN.XN.XN.XN.X12N.XN.XN.X1 & 2 & 3 & 412N.X123N.X12345678910N.X1
20212N.XN.X12N.XN.XN.XN.XN.XN.X1N.XN.X12N.XN.X
2131N.XN.XN.X1N.XN.X1N.XN.X123N.X1N.X1N.X1
224N.X1N.XN.XN.X1N.X123N.X123N.X1N.XN.XN.X
Sheet1
Cell Formulas
RangeFormula
AB6:AT6AB6=IF(COUNTIF(B6:B7,"N.X")=ROWS(B6:B7),B6,N(AA6)+1)
AB12:AT12AB12=IF(COUNTIF(B12:B14,"N.X")=ROWS(B12:B14),B12,N(AA12)+1)
AB19:AT19AB19=IF(COUNTIF(B19:B22,"N.X")=ROWS(B19:B22),B19,N(AA19)+1)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
AB19:AT19Cell Value="N.X"textNO
AB12:AT12Cell Value="N.X"textNO
AB6:AT6Cell Value="N.X"textNO
Click to expand...
Sir Peter, thank you for your gentle help all the formulas worked fine as I needed. (y)

Good Luck to you.

My Best Regards
Kishan 😊
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Solved
Count N.X till find in any of 2 rows
Replies
5
Views
290
Peter_SSs
Peter_SSs
K
  • Solved
Need Summary of N.X and count of N.X no Continuation
Replies
8
Views
602
Kishan
K
K
  • Question Question
Need a code modification as per current requirement.
Replies
2
Views
354
Kishan
K
K
  • Solved
Count continuous “X’s” and covert the numbers into “N.X”
2
Replies
13
Views
765
Peter_SSs
Peter_SSs
A
  • Solved
Count multiples rows and ans in a single row and column
2
Replies
13
Views
148
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,223,912
Messages
6,175,340
Members
452,638
Latest member
Oluwabukunmi

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top