# Conditional transposing and missing values find - urgent!

#### zlobby

##### New Member
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

<tbody>
</tbody>

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

<tbody>
</tbody>

Should become:

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

<tbody>
</tbody>

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!

### Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

#### deanst

##### Board Regular
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
Cell Formulas
RangeFormula
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)), "-"))

#### zlobby

##### New Member
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?

#### deanst

##### Board Regular
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.

Replies
5
Views
334
Replies
6
Views
668
Replies
6
Views
329
Replies
1
Views
160
Replies
0
Views
210

1,195,949
Messages
6,012,483
Members
441,701
Latest member
vnkendijs

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back