Transposing a table

**ElmerS** · 07-13-2009, 06:13 PM

Hi,

All you need, in order to help me out, is explained inside the attached WB.

You may consider to copy/duplicate the 2 Worksheets in order to present the requested formulas.

There are two different questions - one in each WS.

Thanks in advance,

Elm

**JBeaucaire** · 07-13-2009, 07:07 PM

The second sheet is the one I know right off the bat and provide for you.

**ElmerS** · 07-14-2009, 03:53 AM

Thank you.

I hope my customer will tolerate with coloring, the extra Names, in white.

If somebody else can think of a formula to accomplish the same without the need to fill-in all names - I will appreciate it.

Elm

**ElmerS** · 07-14-2009, 07:52 AM

One last question regarding this issue.

Assuming the Names can be filled in - what formulas(s) will take of them if UN-SORTED ?

something like:

DEF
DEF
ABC
ABC
ABC
ABC
ABC
GHI
PQR
JKL
MNO
MNO
MNO

while everyone gets its appropriate fields, of course.

Elm

**JBeaucaire** · 07-14-2009, 12:12 PM

The answer I gave you above already works in this situation. Did you even try it?

The index/match approach I've shown is looking for exact matches in groups. It doesn't care how they are ordered as long as each GROUP of values is ordered together.

As for the duplicate values in another color, you know you could make the duplicates WHITE font, effectively turning them invisible. Then sheet2 option would at least LOOK like your sheet 1, which seems to be the "look" you're after.

**JBeaucaire** · 07-14-2009, 12:24 PM

Another option for automatically suppressing the appearance of the secondary values in column A is a simple Conditional Format.

I highlighted A3:A14, the selected Format > Conditional Format

Condition1: Formula Is: =COUNTIF($A$2:$A3,$A3)>1
Format...Font > White

That made all the secondary values disappear on their own.

**ElmerS** · 07-14-2009, 01:39 PM

Yes, and I was mistaken.

As per the Conditional Formatting, in White, - that is exactly what I had in mind.

Thanks a gain, Elm

**JBeaucaire** · 07-14-2009, 01:43 PM

If that takes care of your need, be sure to EDIT your original post, click Go Advanced and mark the PREFIX box [SOLVED].

(Also, use the blue "scales" icon in our posts to leave Reputation Feedback, it is appreciated)

**ElmerS** · 07-14-2009, 01:56 PM

Believe it or not - this thread was marked as "SOLVED" right after your first reply.

Later, when I noticed I have 1-2 more questions - I removed the "SOLVED" sign.

You are welcome to check all, or most, of my posting to find out that I do mark a closed discussion as SOLVED.

I'm not familiar with any blue "scales" icon.

Please try to explain in details (a picture may also help).

Elm