Thanks:  0
Likes:  0

Thread: Conditional transposing and missing values find - urgent!

1. Conditional transposing and missing values find - urgent!

Well hello, Fellas!

I'm in a dire need of some assistance. My brainz has frozen and I need this yesterday. Here is the case:

 A NAME B NAME NUMBER a 1 number between 32 and 74 (incl.) a 2 56 a 3 32 a 7 55 a 90 44 a 2 74 a 4 66 a 90 70 b 3 32 b 7 33 b 8 34 b 14 35 c 54 36 c 32 37 c 1 38 c 3 39 c 8 40 c 9 55 c 1 70

There are some A NAME with one or more corresponding B NAME. B NAME may be found against more than one A NAME. Each A NAME/B NAME relation can result in only one unique NUMBER value. NUMBER can contain a number from 32 to 74, incl. Each row is unique.

I need to transpose the NUMBER values once per single A NAME-every B NAME horizontally, e.g.

 a 1 33 a 2 56 a 3 32 b 3 32 b 7 33

Should become:

 a 1 33 33 56 32 ... ... ... ... a 2 56 a 3 32 b 3 32 32 33 ... ... ... ... ... b 7 33

And then (or before), I'll need to find the missing values in the range of 32-74.

Any help is greatly appreciated! Oh, almost forgot, let's stick to formulas please, it's important to me. Thanks!

2. Re: Conditional transposing and missing values find - urgent!

Hi - maybe this will help. Fill the formula in D2 down and to the right.

Dean.

Excel 2013
ABCDEFGHIJKLMNOPQR
2a1333356325544746670-------
3a256---------------
4a332---------------
5a755---------------
6a9044---------------
7a274---------------
8a466---------------
9a9070---------------
10b33232333435-----------
11b733---------------
12b834---------------
13b1435---------------
14c543636373839405570--------
15c3237---------------
16c138---------------
17c339---------------
18c840---------------
19c955---------------
20c170---------------

Sheet1

Worksheet Formulas
CellFormula
D2=IF(OR(COUNTIF(\$A\$2:\$A2, \$A2)>1, ROW(OFFSET(\$A1, D\$1, 0))>ROW(\$A\$20)), "-", IFERROR(INDEX(OFFSET(\$C1, D\$1, 0):\$C\$20, MATCH(\$A2, OFFSET(\$A1, D\$1, 0):\$A\$20, 0)), "-"))

3. Re: Conditional transposing and missing values find - urgent!

Hi Dean! You're the man!

I was thinking of something like this but my brain is still not up to the capacity to go that deep.

Anyhow, what happens if I have more than 15 horizontal values in row 1, e.g. 64? What happens if there are duplicates in column C?

Another strange thing, why this \$A\$20 everywhere?

And finally, how do you put such a nice looking table in the post?

4. Re: Conditional transposing and missing values find - urgent!

Hi,

Just drag the columns out to whatever size you need past 15. Better to have too many than not enough.

\$A\$20 and \$C\$20 represent the end of the range that the formula is searching. Just amend the row number (20) to whatever boundary you like. Again, better to have too much than too little so set it to 1000 or something like that.

On posting the tables, I use an add-in from Excel forums | Excel Matters (look at the bottom of the post for the add-in). It converts a selected range to html for pasting into your posts.

Dean.

User Tag List

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•