Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Count matching data
I have a spreadsheet with 14 columns and 20 rows - and what I would like is a formula that will look in a particlular row i.e B1:B14 and return and X in cell B15 if consequtive cells of 4 or more initials are found e.g. if B1:B2:B3:B4 = TT in each cell then the result in B15 would be X and if c3:c4:c5:c6 = MM the result in B16 would be X. If however B1:B2:B3 = TT and B4 = NN then no X is returned (cells will either contain initials or be blank)
is this possible? |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Count matching data
On Mon, 26 Apr 2010 15:58:59 +0100, Barny
wrote: I have a spreadsheet with 14 columns and 20 rows - and what I would like is a formula that will look in a particlular row i.e B1:B14 and return and X in cell B15 if consequtive cells of 4 or more initials are found e.g. if B1:B2:B3:B4 = TT in each cell then the result in B15 would be X and if c3:c4:c5:c6 = MM the result in B16 would be X. If however B1:B2:B3 = TT and B4 = NN then no X is returned (cells will either contain initials or be blank) is this possible? I think you are mixing columns and rows. B1:B14 is part of a column, not a row. Assuming that you have data i 14 rows and 20 columns, in the range A1:T14 and that you what the X's on row 15, i.e. on the row just below your data. Try the following formula in cell A15: =IF(SUMPRODUCT(--(A1:A11<""),--(A1:A11=A2:A12),--(A2:A12<""),--(A2:A12=A3:A13),--(A3:A13<""),--(A3:A13=A4:A14))0,"X","") Copy the formula to the right to cover A15:T15. The result is that you will have an "X" on row 15 if there are at least one occurance of consecutive cells in rows 1 to 14 in the corresponding column that have the same non-blank value. Hope this helps / Lars-Åke |
#3
|
|||
|
|||
Quote:
is this possible? |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Count matching data
On Thu, 6 May 2010 12:22:49 +0100, Barny
wrote: 'Lars-Åke Aspelin[_4_ Wrote: ;949033']On Mon, 26 Apr 2010 15:58:59 +0100, Barny wrote: - I have a spreadsheet with 14 columns and 20 rows - and what I would like is a formula that will look in a particlular row i.e B1:B14 and return and X in cell B15 if consequtive cells of 4 or more initials are found e.g. if B1:B2:B3:B4 = TT in each cell then the result in B15 would be X and if c3:c4:c5:c6 = MM the result in B16 would be X. If however B1:B2:B3 = TT and B4 = NN then no X is returned (cells will either contain initials or be blank) is this possible?- I think you are mixing columns and rows. B1:B14 is part of a column, not a row. Assuming that you have data i 14 rows and 20 columns, in the range A1:T14 and that you what the X's on row 15, i.e. on the row just below your data. Try the following formula in cell A15: =IF(SUMPRODUCT(--(A1:A11<""),--(A1:A11=A2:A12),--(A2:A12<""),--(A2:A12=A3:A13),--(A3:A13<""),--(A3:A13=A4:A14))0,"X","") Copy the formula to the right to cover A15:T15. The result is that you will have an "X" on row 15 if there are at least one occurance of consecutive cells in rows 1 to 14 in the corresponding column that have the same non-blank value. Hope this helps / Lars-Åke have been using this formula and it works well but I have subsequently realised that whilst I need to look for consecutive occurancies, I need to ignore blanks e.g if b1=tt b2=tt b3=blank b4=tt b5=blank b6=tt then b15 =X is this possible? To ignore blank cells I suggest the following solution that makes use of some helper rows. Your original data are still in cells A1:T14 Make sure that all cells in A15:T28 are blank. Put the following formula in cell A29: =IF(ROW()-ROW(A$28)COUNTA(A$1:A$14),"",INDEX(A$1:A$14,SMALL (IF(A$1:A$14<"",ROW(A$1:A$14)),ROW()-ROW(A$28)))) Note: This is an array formula that has to be confirmed with CTRL+SHIFT+ENTER rather than just ENTER. Copy the formula in cell A29 across B29, C29 all the way to T29. Copy cells A29:T29 down all the way to row 42. Finally, put the same formula that you have used before in cell A43, just with modified ranges, like this: =IF(SUMPRODUCT(--(A29:A39<""),--(A29:A39=A30:A40),--(A30:A40<""),--(A30:A40=A31:A41),--(A31:A41<""),--(A31:A41=A32:A42))0,"X","") Copy cell A43 across B43, C43 all the way to T43. Rows 15 to 42 are the helper rows and you can hide them if you like. Hope this helps. / Lars-Åke .. |
#5
|
|||
|
|||
Quote:
rows A1 to A14 which will either contain initials or be blank example - if row A1=tt, A2=tt, A3=MS, A4=tt A5toA14 all =blank then the result is blank row A1=tt, A2=tt, A3=tt, A4=tt, A5to14=bank result is X row A1=tt A2=blank A3=MS A4=tt A5=blank A6=tt A7=tt A8=tt, A9toA14=blank then result=X because in this sequence (A1:A8) there is still a row of consecutive initials A4:A8 (ignoring blank A5) sorry if this isnt clear |
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Count matching data
On Mon, 10 May 2010 13:42:40 +0100, Barny
wrote: 'Lars-Åke Aspelin[_4_ Wrote: ;952052']On Thu, 6 May 2010 12:22:49 +0100, Barny wrote: - 'Lars-Åke Aspelin[_4_ Wrote: - ;949033']On Mon, 26 Apr 2010 15:58:59 +0100, Barny wrote: -- I have a spreadsheet with 14 columns and 20 rows - and what I would- like- is a formula that will look in a particlular row i.e B1:B14 and- return- and X in cell B15 if consequtive cells of 4 or more initials are- found- e.g. if B1:B2:B3:B4 = TT in each cell then the result in B15 would be- X- and if c3:c4:c5:c6 = MM the result in B16 would be X. If however B1:B2:B3 = TT and B4 = NN then no X is returned (cells will either contain initials or be blank) is this possible?-- I think you are mixing columns and rows. B1:B14 is part of a column, not a row. Assuming that you have data i 14 rows and 20 columns, in the range A1:T14 and that you what the X's on row 15, i.e. on the row just below your data. Try the following formula in cell A15: =IF(SUMPRODUCT(--(A1:A11<""),--(A1:A11=A2:A12),--(A2:A12<""),--(A2:A12=A3:A13),--(A3:A13<""),--(A3:A13=A4:A14))0,"X","") Copy the formula to the right to cover A15:T15. The result is that you will have an "X" on row 15 if there are at least one occurance of consecutive cells in rows 1 to 14 in the corresponding column that have the same non-blank value. Hope this helps / Lars-Åke- - have been using this formula and it works well but I have subsequently- realised that whilst I need to look for consecutive occurancies, I need to ignore blanks e.g if b1=tt b2=tt b3=blank b4=tt b5=blank b6=tt then b15 =X is this possible?- To ignore blank cells I suggest the following solution that makes use of some helper rows. Your original data are still in cells A1:T14 Make sure that all cells in A15:T28 are blank. Put the following formula in cell A29: =IF(ROW()-ROW(A$28)COUNTA(A$1:A$14),"",INDEX(A$1:A$14,SMALL (IF(A$1:A$14<"",ROW(A$1:A$14)),ROW()-ROW(A$28)))) Note: This is an array formula that has to be confirmed with CTRL+SHIFT+ENTER rather than just ENTER. Copy the formula in cell A29 across B29, C29 all the way to T29. Copy cells A29:T29 down all the way to row 42. Finally, put the same formula that you have used before in cell A43, just with modified ranges, like this: =IF(SUMPRODUCT(--(A29:A39<""),--(A29:A39=A30:A40),--(A30:A40<""),--(A30:A40=A31:A41),--(A31:A41<""),--(A31:A41=A32:A42))0,"X","") Copy cell A43 across B43, C43 all the way to T43. Rows 15 to 42 are the helper rows and you can hide them if you like. Hope this helps. / Lars-Åke .. thanks for your response on this however, I think my lack of excel ability means i am getting a bit lost. I wondered if we could work on the following basis (assuming the formula is possible) -aim is to identify where there is 4 consecutive rows of initials between A1:A14 but ignoring blank cells: rows A1 to A14 which will either contain initials or be blank example - if row A1=tt, A2=tt, A3=MS, A4=tt A5toA14 all =blank then the result is blank row A1=tt, A2=tt, A3=tt, A4=tt, A5to14=bank result is X row A1=tt A2=blank A3=MS A4=tt A5=blank A6=tt A7=tt A8=tt, A9toA14=blank then result=X because in this sequence (A1:A8) there is still a row of consecutive initials A4:A8 (ignoring blank A5) sorry if this isnt clear This is very clear. I can't do this with a single formula. It is probably possible, but I guess the formula will be very complex, but someone else may come up with something. The formulas that I proposed make use of some helper rows. Have you tried these formulas? Do they give you the result you expect? If it is not, for some reason, possible to have the helper rows on rows 15 to 28 you may have a copy of your original data somewhere else in the sheet, e.g. below all other data. And you can hide these rows as well as the helper rows. Then you can have the final formula on row 15 if you want. There is always the possibility to have a User Defined Function to return the expected result if you can allow macro execution in your workbook. If you do want to try a UDF, here is a proposal Function consecutive(r As Range, m) As Boolean Application.Volatile consecutive = False For i = 1 To r.Rows.Count - m + 1 If r(i).Value < "" Then found = True n = 1 For j = i + 1 To r.Rows.Count If (r(j).Value < "") And (r(j).Value < r(i).Value) Then found = False Exit For End If If found And (r(j).Value = r(i).Value) Then n = n + 1 End If If n = m Then consecutive = True Exit Function End If Next j End If Next i End Function in cell A15 you now put the formula =IF(consecutive(A1:A14,4),"X","") Copy the formula to the right to cell B15:T15 Hope this helps / Lars-Åke |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Matching identical data using data only once in the matching proce | Excel Discussion (Misc queries) | |||
How to count for matching requirement? | Excel Discussion (Misc queries) | |||
matching the column b(sku)c(count)with A | Excel Worksheet Functions | |||
Matching data and linking it to the matching cell | Links and Linking in Excel | |||
How to count matching text | Excel Discussion (Misc queries) |