Groupuing User Requirement

[Atif]

Hi,

Source data is in following form, depending on the user reqirement, I want
to creat a Packate Code, i.e. Standard Desktop (A), Standard Desktop with
Monitor (B), Standard Laptop (C). Standared Laptop with Monitor (D)

Source:
A B
1 User Name Hardware
2 BILINSKI, JOHN Standard Desktop
3 Standard 17" Monitor
4 ZMIJOWSKYJ, MIKE Standard Laptop
5 MOORE, LIZ Standard Desktop
6 CHEUNG, JULIANA Standard Laptop with
Standard 17" Monitor

Result will be:
A B
10 BILINSKI, JOHN B
11 ZMIJOWSKYJ, MIKE C
12 MOORE, LIZ A
13 CHEUNG, JULIANA D

Regards

Atif

 

[Rob van Gelder]

A problem is that the Person's name in column A is not repeated down.
Is there a possibility that you could repeat the name?

If so, the formula becomes quite easy.

Cheers,
Rob

 

[Atif]

Thank you Rob.

Names can not be repeated and I am looking for VBA solution.
If Names can be repeated Will you please share your idea.

 

[Rob van Gelder]

Let's say your data is in two columns A and B.

Your data looks as follows:
User Name Hardware
BILINSKI, JOHN Standard Desktop
BILINSKI, JOHN Standard 17" Monitor
ZMIJOWSKYJ, MIKE Standard Laptop
MOORE, LIZ Standard Desktop
CHEUNG, JULIANA Standard Laptop
CHEUNG, JULIANA Standard 17" Monitor


Assuming that cell A10 contains your user name, use the following formula:

=CHOOSE(SUM(($A$2:$A$7=$A10) * (IF($B$2:$B$7="Standard 17"" Monitor", 1, IF($B$2:$B$7="Standard Desktop", 2, IF($B$2:$B$7="Standard Laptop", 4))))),
"", "A", "B", "C", "D")

It's an array formula, so use Ctrl+Shift+Enter when you enter the formula.

Cheers,
Rob