Conditional transposing and missing values find - urgent!

zlobby

New Member
Joined
May 24, 2015
Messages
2
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 NAMEB NAMENUMBER
a1number between 32 and 74 (incl.)
a256
a332
a755
a9044
a274
a466
a9070
b332
b733
b834
b1435
c5436
c3237
c138
c339
c840
c955
c170

<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.

a133
a256
a332
b332
b733

<tbody>
</tbody>

Should become:

a133335632............
a256
a332
b3323233...............
b733

<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

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
Hi - maybe this will help. Fill the formula in D2 down and to the right.

Dean.


Excel 2013
ABCDEFGHIJKLMNOPQR
1A NAMEB NAMENUMBER123456789101112131415
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)), "-"))
 
Upvote 0
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? :)
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,528
Messages
6,114,154
Members
448,553
Latest member
slaytonpa

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top