Thread: Cell Check With Numbers, Symbols and Characters

Hello,

I have spent some time searching for a past thread related to my question and have only found a few forums with similar questions but not the same. I then tried using some VBA online lessons and such but have had no luck - I appreciate any help provided.

I have a spreadsheet that is already sorted and does not require further sorting. I essentially require the macro to take the contents of a cell and check it with the cell below it. The problem is the cells contain a mix of mostly numbers, symbols, and a consistent capital letter.

'1120500+1256-R
'1086900+1247-R

What would be the command to check these two strings?

Thanks a million,

Alex

Welcome to the forum.

Check what?

Hey shg - sorry for the lack of information within my question.

When I say check it would consist of checking to see if the two strings are the same or different.

If same, conduct a multiplication operation, if different, take an average of the multiplication operations within the range where the cells were the same

                   COLUMN 1               COLUMN 2                 COLUMN 3

ROW 2     1120500+1256-R                5.2                             2.4
ROW 3     1120500+1256-R                5.0                             2.5
ROW 4     1120500+1256-R                4.5                             2.4
ROW 5     1086900+1247-R                30.0                           5.0
ROW 6     1086900+1247-R                30.0                           5.5

ie) Some sort of loop that compares C1R2 with C1R3, if they are the same, multiply C2R2 with C3R3. If C1R4 does not equal C1R5, averages the multiplication operation between C2 and C3 in the rage where column 1 is the same.

I think i know how to program the loop but I'm unsure of how to to do the check within column 1 and then when they are dissimilar, average the multiplication operation within the range where they were similar.

Thanks a million for any help.

Regards,

Alex

      ------A------- --B--- --C-- ---D--- ------------------E------------------
  1   Col 1          Col 2  Col 3                                              
  2   1120500+1256-R   5.2   2.4    12.5  D2 and down: =IF(A2=A3, B2*C2, "???")
  3   1120500+1256-R   5.0   2.5    12.5                                       
  4   1120500+1256-R   4.5   2.4   ???                                         
  5   1086900+1247-R  30.0   5.0   150.0                                       
  6   1086900+1247-R  30.0   5.5   ???

If C1R4 does not equal C1R5, averages the multiplication operation between C2 and C3 in the rage where column 1 is the same.

I don't understand that part, hence the "???" in the formula.

Thank you for your response.

In row 4, it would conduct the multiplication as normal ie) ??? = 10.8 but the average would be taken on all "1120500+1256-R"
Hence the check would be to ensure the range of 1120500+1256-R is calculated.

The average would be taken across all 1120500+1256-R.

Does that clarify anything?

Maybe ?
In D2

=IF(A2=A3,B2*C2,SUMIF($A1:D$2,A2,$D1:D$2)/COUNTIF($A1:D$2,A2))

Drag/Fill Down

or in 2007 and above

=IF(A2=A3,B2*C2,AVERAGEIF($A1:D$2,A2,$D1:D$2))

[EDIT]
Just saw your post
Try

=IF(A2=A3,B2*C2,(SUMIF($A1:D$2,A2,$D1:D$2)+B2*C2)/(COUNTIF($A1:D$2,A2)+1))

Hope this helps

Yes, I believe that would work, thank you!

BioEngineer, please don't quote whole posts -- it's just clutter.

Thanks.