Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have a column of data like column B below. The letter "A" denotes software, "B" a service and "C" hardware. How do I match each product with its category to make it look like the table below?

   A        B
Software    A1
Software    A2
Software    A3
Software    A4
Service     B1
Service     B2
Service     B3
Service     B4
Hardware    C1
Hardware    C2
Hardware    C3
Hardware    C4

How would I achieve this in Excel?

share|improve this question
up vote 3 down vote accepted

Copy this formula to A1 and use AutoFill down.


The formula consists of 3 formulas nested together.

share|improve this answer
nixda, how would I do this if I wanted to do something like a top 10 list. For example, I have a list of names in A, a count of something in columb B. I want to the top 5 names and counts in columns C and D. – justingrif Oct 26 '13 at 4:03
@justingrif Can you please ask this as a new question? I think the solution would be different to this one – nixda Oct 26 '13 at 7:11

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.