Checking data in each cells of an array

[cyzax7 via OfficeKB.com]

hi,

I have a sheet1 that looks like this: (the data is defined in an array)

Column A Column B .......
......Column n

Row1 5 25
Row2 6 30
Row3 7 65
Row4 8 56

 

[tony h]

Ok so you have data in an array. You need to put in a bit more effor
into explaining what is the problem you are trying to resolve.

Friendly comment,
cheer

 

[tony h]

Ok so you have data in an array. You need to put in a bit more effor
into explaining what is the problem you are trying to resolve.

Friendly comment,
cheer

 

[cyzax7 via OfficeKB.com]

hi tony,

thanx 4 ur responze.

I want to compare each value (in each cells) in column A of sheet1 to column
A of sheet2. Then, if the value of a specified cell is same, it will compare
the value in the row of the cell in sheet1 to the value in the row of the
cell in sheet2. I think its better if I put an example: ;>

if Cells (Row3, Column A) in sheet 1 = Cells (Row 7, Column A) of sheet2 ;

Then it will take the other value in Row3 of Column B,Column C,Column D, ...
Column n of sheet 1 and compare it with the other value in Row7 of Column B,
Column C,Column D, ...Column n of sheet 2.

ive tried so many way, but it doesn't work..

 

[tony h]

What are you going to do with the results of all your comparing?

Do you need this appearing on a worksheet (if so where) or do you want
a macro to run and offer you a result?

 

[Abanana]

I am wondering if I could ask for your expertise

I know very little about Excel

I wanted to ask how or if possible to turn alphabets into number

for example:

cell A1 has LCDU and cell B1 has AL cell C1 LDENP

suppose LCDU means 8 AL means 0 LDENP means 12

and I want to sum row 1 to give me a total of 20

I anticipate my appreciation for your help

 

[cyzax7 via OfficeKB.com]

Hi tony,

it will take the other value in Row3 of Column B,Column C,Column D, ...
Column n of sheet 1 and compare it with the other value in Row7 of Column B,
Column C,Column D, ...Column n of sheet 2.

1. If the value in sheet 1 (Row3 of Column B,Column C,Column D, ... Column n)
is greater than or equal (>=) to the value in sheet2 (Row7 of Column B,
Column C,Column D, ...Column n).

2. The results of the comparison will be displayed in a new sheet (let say
sheet 3) . In the result sheet, if the condition I stated in (1) is true, it
will be displayed in blue color and if it is false, it will be displayed in
red color.

3. Right now, the main things that I want to figure out is how to compare the
value in each cells of column A (in sheet1) to the value in each cells of
column A (in sheet 2).

I really need ur help..to be honest, i'm so new to xcel macro programming..
juz about 1 and half month.. nway , thanx for ur response

 

[Abanana]

Please press contr+A to copy it and paste it on a worsheet to see it
more properly


86 N SD CORD AL ENP SUPER ML E L S in this colmn
should show how many hours each employee worked
DATE
FROM: Mon Tue Wed Thu Fri Sat Sun Mon Tue Wed Thu Fri Sat Sun Mon Tue Wed Thu Fri Sat Sun Mon Tue Wed Thu Fri Sat Sun
June/July
06 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 1 2
Dawood
Mary SD 9-5 9-5 SD SD LENP ENP AFC AFC AFC AFC AFC ENP ENP
Gamston Julia
10-6 8-4 ENP 8-4 ENP ENP10-6 8-4 8-4 ENP10-6 8-4
Taylor Iain
Nicky
Adams LDENP N N V V AL AL AL AL AL AL LD LD ELENP ELENP
Baster Victoria
Bryant
Ceri 8-13 L N N V V AL AL AL LD LDENP LDENP LD N N N V
Gallagher Geri LD LD V V LD N N N N LD LD V V LD LD LD LD
Jaye
Charlotte L 8-14 LDENP 14-20.30 EENP LD LDENP LEENP
Kerr
John V LDENP LDENP E/LENP N N LD LD V V AL AL AL AL AL AL
Martin
Wendy AL AL AL LDENP LDENP 20-02ENP V V 20-02ENP
Ritchie
Lara LCDU ECDU LCDU LCDU ECDU LDENP LDENP EENP/L 11-2030 LD N N N N N V V V V
Ryan
Audrey LDENP SD L N N LD V V LD LD LDENP LDENP V V N N N N
Sharman
Annemarie ML ML ML ML ML ML ML ML ML ML ML ML
Woodward
Carol N LD LD LD LCDU ECDU LCDU LCDU ECDU N N N AL AL AL
Catudio
Lorenzo LD LD N V LD LD LD V V V LD N LD LD LD LD N
Dean H Sarah V LD LD LD LD LD LD V V V LD SD N N N N LD
Duffy
Bernice N EENP EENP N V EENP ROTA8-17 N N N N LD LD V V EENP EENP N
Harding
Pam AL AL AL N V LD LD V LD N N LDENP LD LD LDENP V DO
Hodnett Caz AL AL AL AL AL AL LD LD N LD LD N N V V V
Rumming
Callum PDS PDS PDS PDS PDS PDS PDS PDS PDS PDS PDS PDS
Taylor Nikki V LD LD LD LD N N N V V AL AL AL AL AL AL
Whatley
Nicky N N N LD LENP ROTA8-17 EENP LD N N LENP E LD V LD LENP V V V
Bird
James LD L LD SD N N N SD N PDS PDS PDS PDS PDS PDS V V
Colombi Massimo N N LD L LD DO LD N N V V V V AL AL AL
Craddock Lydia LD LD SD LD LD N N SD V LD LD LD V V E N N V
Cruz
Hazel LD LD LD SD LD 8-12.30 LD LD V V V N N N N LD LD
Davey
Stella N N N N LD LD V LD LD SD LD 14-2030 LD LD LD V V
Dyer Hazel LD LD N N LD LD LD V V V V LD LD LD LD LD LD
Emanuele Alice V V V LD LD N N N LD LD LD LD V N N N LD
Green Wendy E LD SD LD LD N N V V V V LD LD SD N N LD LD
Leen Fiona AL AL AL LD LD LD AL AL AL LD LD N N
Malana Ron N N N N LD N N N LD V V V LD LD AL AL AL
Mosquera Eric V V LD N LD N N N LD LD LD LD LD LD
Proven Nicole S/D E L SD V V V L L E E L N N L L L L E L L
O’Sullivan Kate SD N N N N N N LD SD LD LD LD LD SD LD V V
Stockman Charlie AL AL AL AL AL AL N N N N N N N
White Sunny LD LD LD LD LD V V LD V LD V LD LD N N N
Cobb Hannah PDS PDS PDS PDS PDS PDS N N LD LD LD
LD
Edoloverio Rogelio N N N N N N N N N N N N
Foley Aiden AL AL AL N N LD V V N N N N N V V LD LD
Fox Charlotte SD/L LD N N LD LD V V LD LD LD N N LD LD V V
Grogan Ellen N N LD LD V V N N LD LD LD V LD V AL AL AL
Hayden Valerie N N N V V LD SD LD LD N LD LD V V LD LD LD
Ismail Deka LD LD LD LD LD N N N LD LD N N LD
Kirsopp Aimee N N N SD V V AL AL AL N N N SD LD N N V V
McLaughlin Anna
LD N N AL AL AL SD N N V LD V 8-1230 N N N V V
Modronio Denis
V LD LD LD LD V N N N N V LD LD LD V LD LD N
Naughton Eileen
SD LD L V E LD LD LD V LD LD LD V V N N N N
Okahire Kingsley LD LD LD LD LD LD N N N LD LD N N
Reyes Marilou LD LD LD LD AL AL AL LD LD LD LD LD
Stephens
Caroline LD SD LD LD V V V N N N AL AL AL AL AL AL
Thomas Christine
LD LD LD LD SD N N LD LD N N 8-1230 V V N LD
Wagstaff Sophie
LD LD 8-1230 N V V AL AL AL SD N N N LD N N
Wilson
Sarah SD LD LD V V LD LD LD LD 8-1230 LD LD 8-1230 LD LD LD LD V V
Zamir
Florin AL AL AL V V V V AL AL AL AL LD LD LD LD LD LD
Baig Mirza V V V V N N N N N N N N LD LD LD N N
Fiddy Damon LD LD LD N N N N N N N N N N V V V
Pirnau Bogden N N N LD LD LD LD LD N N N N LD
Rendon Alex
V V V V LD LD LD LD LD LD LD LD LD LD LD LD LD
Neal Showla LD LD LD LD
Belkaid Blu AL AL
Millis William
10-6 10-6 10-6 10-6 10-6 10-6 10-6 10-6
Early 10 10 10 10 10 10 10 9 10 9 10 9 9 10 10 9 10 9 10 10 9 9 10 11 10 10 10 10 A66:68
this rows gives the number of employees working during the day and
night
Late 10 11 10 11 10 10 11 12 10 9 10 9 9 9 10 9 10 10 9 10 10 9 10 10 11 10 9 11
Night 6 7 7 6 7 7 7 7 7 8 7 8 7 8 8 7 8 7 8 8 8 7 6 6 7 7 8 7

 

[tony h]

Dear Abanana,

there are various ways to do this but the simplest (possibly) is to set
up a table of the alpha strings in one column and their numberical value
in the second
eg
LCDU 8
AL 0
LDENP 12


make sure these are sorted alphabetically.

Then set up a second column for each original column and use the
Vlookup function (of which there are plenty of examples in this forum)
to translate the original codes to numerical values using the table you
set up.

It is then a simple matter to add up the values in the column.

Hope this helps

 

[Abanana]

I appreciate your answer
Thanks