1

I am attempting to find a way to search rows and find out which ones have multiple values in the rows. For example If I have this:

81  IRENE L MOTZ                        
82  BRITTNEY SUMMERVILLE                        
83  NICK SIRK   33  120             1,859                        
86  DOROTHY TARR                        32    
87  DAVID BRYANT                        
89  WAYNEDALE VAULT                     
90  APRIL HOWARD                        
95  DONA KAHL                       
96  ASHLEY FISHER                       
98  TERRI SULESKI                       
99  SCOTTSVILLE VAULT                       
104 CHRISTINE WIECHART      564             
105 KAYLENE DUNCAN      7               
106 LINDA NETHERTON                     
107 DIANNA MAY              336     
108 DIXIE HETTINGER     596             
110 JIM MELVIN      26              
111 LASHAWNYA BREWER        181             
112 LOIS COLEMAN        283             
113 KRISTIN FRIEND      827             
115 BRANDI QUDUS        2,106

You can see that the row starting with 83 has three values in the columns that follow the name. While the others in this example only have one value. I need to be able to find the rows that have multiple values, then I'm going to perform some actions on the values in the columns (mostly just adding them together and then subtracting the total from a previously calculated number). I don't have any code at the moment, if some one could point me in the right direction that would be great. I though maybe trying to do it with IF ELSEIF statements, but that just seemed sloppy to me.

Community
  • 1
  • 1
Mike
  • 1,853
  • 3
  • 45
  • 75
  • check for space characters - then parse out each value... seems like it might be beyond what you typically do. – Randy Dec 24 '13 at 13:38
  • 2
    can't you just use the 'text to column' command using space as separator? – arilia Dec 24 '13 at 13:38
  • Another option would be to create another sheet with the names in one column, 'COUNTIF{}' or 'SUM()' in the second column, and the calculation in the third (you could also roll up the calculation into the second column). – Chris Dec 24 '13 at 13:50
  • I'm not trying to sort the values, they are already in different columns. I need to find the rows that have values in multiple columns. Most rows have the "Teller Code"(A), "Name"(B),then one value in one of the next 6 columns (C-H). I need to find the rows where there are values in two or more columns in C-H on a single row. – Mike Dec 24 '13 at 13:51
  • Not sure how to make the spread sheet info line up correctly, but the numbers that come after the names are in different columns not in the same column as the names. – Mike Dec 24 '13 at 13:55

1 Answers1

2

Assuming your data range starts at B1, you enter in A1 the formula

=COUNT(B1:I1)-1

(you mentioned data would go up to column H, I moved it one column to the right) or

=COUNT(C1:I1)

This gives 0 for the first row, as expected. Copying and pasting the formula downwards gives the number you are looking for, for each row (all values <=1, except for row 3). Then you can filter, or perform other actions.

For instance, to get the sum of all numbers in the row (as you mentioned), you would use

=SUMPRODUCT(--(ISNUMBER(C1:I1)),C1:I1)

If you want to calculate this only for rows having more than one number, you can nest this formula inside an IF(B1>1,...

Notes:

  1. This avoids using VBA.

  2. I have copied the data you posted, then pasted in cell B1 of a new sheet. Then Data -> Text to Columns -> Delimited -> (Tick Space). This rendered data in separate columns, as it appears you be your case.

  3. The ranges referred to should be modified, depending on the organization and location of your data.

sancho.s ReinstateMonicaCellio
  • 14,708
  • 20
  • 93
  • 185
  • I only used the "=count" part, and used vba to copy it into the rest of the rows that I needed. Thanks, I should have thought of that. – Mike Dec 24 '13 at 16:53