Hi all,

If I have seven categories in Column A and I want to return a reference
number for each category in colum B. is there a VBA module to achieve this or
it is better with Vloop up?

The table looks like this.

Column A Column B
_________ ____________
Office Equip 100
Ops Equip 200
SHE Equip 300
Security Equip 400
It Equip 500
Comms Equip 600
Vehicels Equip 700


any help on this is appreciated

Best regards
hoshyar

I would use VLOOKUP every time!

e.g =Vlookup("IT Equip", A:B, 2, False)

would return 500

Any good? You can of cause change "It Equip" in above to any cell!


"Hoshyar" wrote:

Hi all,

If I have seven categories in Column A and I want to return a reference
number for each category in colum B. is there a VBA module to achieve this or
it is better with Vloop up?

The table looks like this.

Column A Column B
_________ ____________
Office Equip 100
Ops Equip 200
SHE Equip 300
Security Equip 400
It Equip 500
Comms Equip 600
Vehicels Equip 700


any help on this is appreciated

Best regards
hoshyar

I'd say use a vlookup. Not only a lot faster, but also more user
friendly.

=VLOOKUP("SHE Equip",A1:B8,2,FALSE)

Cheers,
Peter

I would suggest this is a job for VLOOKUP. VBA is overkill.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Hoshyar" wrote in message
...
Hi all,

If I have seven categories in Column A and I want to return a reference
number for each category in colum B. is there a VBA module to achieve this
or
it is better with Vloop up?

The table looks like this.

Column A Column B
_________ ____________
Office Equip 100
Ops Equip 200
SHE Equip 300
Security Equip 400
It Equip 500
Comms Equip 600
Vehicels Equip 700


any help on this is appreciated

Best regards
hoshyar

Hi DeanO and xPete,

many thanks for your prompt answer. however, this is still not working as I
wanted. These categories may appear sevral times in column (A1:A2000). So
the argument will be. in each cell of B1:B2000 I want to see the reference
number specified to its category. Does this formula works? =Vlookup ("IT
Equip","SHE Equip", "Comms Equip", A:B,2,False)

Please advise
Hoshyar

"DeanO" wrote:

I would use VLOOKUP every time!

e.g =Vlookup("IT Equip", A:B, 2, False)

would return 500

Any good? You can of cause change "It Equip" in above to any cell!


"Hoshyar" wrote:

Hi all,

If I have seven categories in Column A and I want to return a reference
number for each category in colum B. is there a VBA module to achieve this or
it is better with Vloop up?

The table looks like this.

Column A Column B
_________ ____________
Office Equip 100
Ops Equip 200
SHE Equip 300
Security Equip 400
It Equip 500
Comms Equip 600
Vehicels Equip 700


any help on this is appreciated

Best regards
hoshyar

Hoshyar,

If I've understood your requirement correctly, it is as follows:

(a) You have a column of data with 0 < rowcount < 2000 or thereabouts

(b) A valid entry in the column must be one of seven category names, as
follows:
1. Office Equip
2. Ops Equip
3. SHE Equip
4. Security Equip
5. It Equip
6. Comms Equip
7. Vehicels Equip

(c) For each row in your column of data, you want to return a
corresponding reference
number, as per the following:
Category Ref
1. Office Equip 100
2. Ops Equip 200
3. SHE Equip 300
4. Security Equip 400
5. It Equip 500
6. Comms Equip 600
7. Vehicels Equip 700

I would do it like this:

1. Copy your column of data to the range A1:An where n is the count of
rows
2. In the range D1:D7, enter the category names
3. In the range E1:E7, enter the corresponding reference numbers
4. Sort the range D1:E7 on "Column A"
5. In cell B1, enter the formula: =VLOOKUP(A1,$D$1:$E$7,2)
6. Copy the contents of cell B1down to cell Bn where n is the count of
rows
7. You should now see the correct reference numbers in column B.

If you want to get more sophisticated, you can also validate column A
values by comparing its contents with a VLookup on column 1 of the
lookup range, and also by trapping #N/A with ISNA. Drop me a line at
fisherofsouls AT (not cold !)mail.com if you want to know more.

Regards

Nick

No

Not sure what you mean. Non-unique in Col A, so is each ref in Col B
different for categories in Col A. Need more details???

"Hoshyar" wrote:

Hi DeanO and xPete,

many thanks for your prompt answer. however, this is still not working as I
wanted. These categories may appear sevral times in column (A1:A2000). So
the argument will be. in each cell of B1:B2000 I want to see the reference
number specified to its category. Does this formula works? =Vlookup ("IT
Equip","SHE Equip", "Comms Equip", A:B,2,False)

Please advise
Hoshyar

"DeanO" wrote:

I would use VLOOKUP every time!

e.g =Vlookup("IT Equip", A:B, 2, False)

would return 500

Any good? You can of cause change "It Equip" in above to any cell!


"Hoshyar" wrote:

Hi all,

If I have seven categories in Column A and I want to return a reference
number for each category in colum B. is there a VBA module to achieve this or
it is better with Vloop up?

The table looks like this.

Column A Column B
_________ ____________
Office Equip 100
Ops Equip 200
SHE Equip 300
Security Equip 400
It Equip 500
Comms Equip 600
Vehicels Equip 700


any help on this is appreciated

Best regards
hoshyar

Hi Nick,

Many thanks, the formula you seggest has achieved what I wanted. I have also
sent you an email on your (very hot) mail.com. I would appreciate if you can
have a look at it.
I would also thank all you guys who made efforts in helping me.
Best regards
Hoshyar

" wrote:

Hoshyar,

If I've understood your requirement correctly, it is as follows:

(a) You have a column of data with 0 < rowcount < 2000 or thereabouts

(b) A valid entry in the column must be one of seven category names, as
follows:
1. Office Equip
2. Ops Equip
3. SHE Equip
4. Security Equip
5. It Equip
6. Comms Equip
7. Vehicels Equip

(c) For each row in your column of data, you want to return a
corresponding reference
number, as per the following:
Category Ref
1. Office Equip 100
2. Ops Equip 200
3. SHE Equip 300
4. Security Equip 400
5. It Equip 500
6. Comms Equip 600
7. Vehicels Equip 700

I would do it like this:

1. Copy your column of data to the range A1:An where n is the count of
rows
2. In the range D1:D7, enter the category names
3. In the range E1:E7, enter the corresponding reference numbers
4. Sort the range D1:E7 on "Column A"
5. In cell B1, enter the formula: =VLOOKUP(A1,$D$1:$E$7,2)
6. Copy the contents of cell B1down to cell Bn where n is the count of
rows
7. You should now see the correct reference numbers in column B.

If you want to get more sophisticated, you can also validate column A
values by comparing its contents with a VLookup on column 1 of the
lookup range, and also by trapping #N/A with ISNA. Drop me a line at
fisherofsouls AT (not cold !)mail.com if you want to know more.

Regards

Nick