Conditional transposing and missing values find - urgent!

[zlobby]

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!

 

[deanst]

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

Dean.

Excel 2013
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	A NAME	B NAME	NUMBER	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15
2	a	1	33	33	56	32	55	44	74	66	70	-	-	-	-	-	-	-
3	a	2	56	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-
4	a	3	32	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-
5	a	7	55	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-
6	a	90	44	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-
7	a	2	74	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-
8	a	4	66	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-
9	a	90	70	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-
10	b	3	32	32	33	34	35	-	-	-	-	-	-	-	-	-	-	-
11	b	7	33	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-
12	b	8	34	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-
13	b	14	35	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-
14	c	54	36	36	37	38	39	40	55	70	-	-	-	-	-	-	-	-
15	c	32	37	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-
16	c	1	38	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-
17	c	3	39	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-
18	c	8	40	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-
19	c	9	55	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-
20	c	1	70	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-
Sheet1

Cell Formulas
Range	Formula
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]

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]

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.