Code To Move From Vertical To Horizontal?

[Dazzawm]

I have sheet 1 as laid out below. Column A will have a list of numbers the same (then they change) with different numbers next to them. I need them to be put on sheet 2 with the numbers in column B next to them with a slash and a gap added, rather than in a list, like the result in sheet 2 and the same when the number in A changes and so on.... Thanks

Before Code

Excel 2010

	A	B
MS001
MS001
MS001
MS001
MS001	9609992380
MS001	377 906 309C
MS002
MS002
MS002
MS002

<tbody>
[TD="align: center"]8[/TD]

[TD="align: right"]46531222[/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]60811067[/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]60814507[/TD]

[TD="align: center"]11[/TD]

[TD="align: right"]500309838[/TD]

[TD="align: center"]12[/TD]

[TD="align: center"]13[/TD]

[TD="align: center"]14[/TD]

[TD="align: right"]60811534[/TD]

[TD="align: center"]15[/TD]

[TD="align: right"]16137039[/TD]

[TD="align: center"]16[/TD]

[TD="align: right"]5234313[/TD]

[TD="align: center"]17[/TD]

[TD="align: right"]33000153[/TD]

</tbody>

Sheet1​

After Code

Excel 2010

	A	B
MS001	46531222/ 60811067/ 60814507/ 500309838/ 9609992380/ 377 906 309C
MS002	60811534/ 16137039/ 5234313/ 33000153

<tbody>
[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]

</tbody>

Sheet2​

 

[DocAElstein]

Hi,
. Just back in this old Thread, as buy coincidence an alternative solution to a thread I answered gave a Formula which can be applied nicely to give a (CSE) Spreadsheet Formula alternative to the Codes given here. I wrote this formula as part of the learning to understand the Formula given in that Thread, and thought it would be a worthwhile contribution here.
. The main Formula is concerned with the main Theme of this thread (vertical list with Duplicate names consolidated with entries concatenated in one cell). This Formula is given in Column D and has its origin in the Formula given in This Thread (Thanks Dave!) in Post #9
Copy cells going down a column with the same patient number to a single row.
. A few other “Help” formulas were needed in Columns C E and F. I expect a Profi could consolidate all into one Formula, (which would be great!!), but I have already over-stretched my Beginner’s brain on getting this far)
. Columns A an B contain sample of the OP’s sample data.

. Because of Browser/Mr Excel Editor problems/issues I Present the table in this Post (Post #31) , and an attempt at a “colorful” explanation of the Formulas, as best I can, in the next Post(Post #32).

. Alan


………………

So:-
.1) The results and Formulas

.1a) The following table gives output results along with the first row of formulas, which simply need to be copied (dragged down) as far as necessary for the complete Solution. For convenience I have arrange using identical Sample data used by apo in Post #29 as I obtain very similar results to those used by apo, and his Achieved Output Table in post #29 which is somewot clearer to see. (His Final Output E Column is almost identical to my Final Output Column F)

.1b) Here is my current working File for the Formula work described in this and the next Thread. (XL 2007 VerticalToHorizontal.xlsx (Sheet of Interest “ForMrExcelFred”)):
https://app.box.com/s/r6ptaai08xobj5qx6irbvxhzfuunyjvn

Book1
	A	B	C	D	E	F
1	NUMBER		Unique Numbers
2	MS001	46531222	MS001	46531222 / 60811067 / 60814507 / 500309838 / 377 906 309C / / / /	46531222 / 60811067 / 60814507 / 500309838 / 377 906 309C	46531222 / 60811067 / 60814507 / 500309838 / 377 906 309C
3	MS001	60811067	MS002	/ / / / / / / /		60811534 / 16137039 / 5234313 / 33000153 /
4	MS001	60814507	MS003	/ / / / / / / /		423423 / 6453643 / 2432 / 76574585 / 22
5	MS001	500309838		/ / / / / / / /
6	MS001	9609992380		/ / / / / / / /
7	MS001	377 906 309C		/ / / / / / / /
8	MS002	60811534		60811534 / 16137039 / 5234313 / 33000153 / / / / /	60811534 / 16137039 / 5234313 / 33000153 /
9	MS002	16137039		/ / / / / / / /
10	MS002	5234313		/ / / / / / / /
11	MS002	33000153		/ / / / / / / /
12	MS003	423423		423423 / 6453643 / 2432 / 76574585 / 22 / / / /	423423 / 6453643 / 2432 / 76574585 / 22
13	MS003	6453643		/ / / / / / / /
14	MS003	2432		/ / / / / / / /
15	MS003	76574585		/ / / / / / / /
16	MS003	3453		/ / / / / / / /
17	MS003	22		/ / / / / / / /
ForMrExcelFred

Cell Formulas
Range	Formula
E2	=SUBSTITUTE(D2," / / ","")
F2	=IFERROR(VLOOKUP(C2,A1:E17,5,FALSE),"")
C2	{=IFERROR(INDEX($A$2:$A$17, MATCH(0, COUNTIF(C$1:$C1, $A$2:$A$17), 0),1),"")}
D2	{=IFERROR(IF($A2=$A1,"",INDEX(IF($A2=$A2:$A$17,$B2:$B$17,""),COLUMNS($A:A),1)),"") & " / " & IFERROR(IF($A2=$A1,"",INDEX(IF($A2=$A2:$A$17,$B2:$B$17,""),COLUMNS($A:B),1)),"") & " / " & IFERROR(IF($A2=$A1,"",INDEX(IF($A2=$A2:$A$17,$B2:$B$17,""),COLUMNS($A:C),1)),"") & " / " & IFERROR(IF($A2=$A1,"",INDEX(IF($A2=$A2:$A$17,$B2:$B$17,""),COLUMNS($A:D),1)),"") & " / " & IFERROR(IF($A2=$A1,"",INDEX(IF($A2=$A2:$A$17,$B2:$B$17,""),COLUMNS($A:F),1)),"") & " / " & IFERROR(IF($A2=$A1,"",INDEX(IF($A2=$A2:$A$17,$B2:$B$17,""),COLUMNS($A:G),1)),"") & " / " & IFERROR(IF($A2=$A1,"",INDEX(IF($A2=$A2:$A$17,$B2:$B$17,""),COLUMNS($A:H),1)),"") & " / " & IFERROR(IF($A2=$A1,"",INDEX(IF($A2=$A2:$A$17,$B2:$B$17,""),COLUMNS($A:I),1)),"")& " / " & IFERROR(IF($A2=$A1,"",INDEX(IF($A2=$A2:$A$17,$B2:$B$17,""),COLUMNS($A:J),1)),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[DocAElstein]

<o> </o>
So:-<o></o>
.2) My best attempt at explanation of Formulas from Post #31 above.<o></o>
<o> </o>
<o> </o>
<o> </o>
<o> </o>
<o> </o>
Going backwards as it were taking the Four Formulas.. <o></o>
<o> </o>
2a) Column F:<o></o>
<o> </o>
=IFERROR( VLOOKUP( C2,A1:E17,5,FALSE ) ,"" )<o></o>
<o> </o>
, The Formula In Column F is fairly simple. A standard VLOOKUP using Column C as the first argument Look Up Value and the second argument Look Up Table (Grid / Range ) being A1:E17 . This Grid is chosen to incorporate the Index 5 as Third argument for the value for Final Output the column in which this formula is in ( columnF ).<o></o>
. Finaly an IFERROR is tacked on simply to as an error is caused when we overshot downwards with the formula past the Look Up Values (In this case when we go past MS003 in Column C , This error is then replaced with “” , which is an empty cell. (The final argument FALSE stipulates an exact entry but the formula appears to work with TRUE also.)<o></o>
. ( Luckily, by default, the formula only gives us the first match and not multiple answers to give us extra rows with lots of “ / / / / “ in them ! )<o></o>
<o> </o>
<o> </o>
2b) ColumnE: <o></o>
<o> </o>
=SUBSTITUTE(D2," / / ","")<o></o>
<o> </o>
. Extremely Simple. Just replacing unwanted extra separator characters “ / “ in the main Formula in Column D with nothing ( “” ) - effectively removing them!<o></o>
<o> </o>
<o> </o>
2c) ColumnD:<o></o>
<o> </o>
. Getting at columns C and in particular C which was the crux of the problem and is based on the Formula Reference ( Post #7 )<o></o>
http://www.excelforum.com/excel-general/1059434-copy-cells-going-down-a-column-with-the-same-patient-number-to-a-single-row.html<o></o>
<o> </o>
. For clarity, just taking the first part which demonstrates the full formula which is just lots of concatenations of this Formula..<o></o>
<o> </o>
{=IFERROR( IF( $A2=$A1,"",INDEX( IF($A2=$A2:$A$17,$B2:$B$17,""),COLUMNS($A:A), 1 ) ) ,"")}<o></o>
<o> </o>
<o> </o>
. I Start with the IF (IF I may! ). -<o></o>
. The IF gives a null entry if the number in the first column is not the next unique value. ( That takes care of not doing anything if we have not hit a Unique Number , so all vertical lines are empty other than just one for each unique value of the entries in column A.<o></o>
. However IF it is the next unique number ($A2<>$A1) then the main business starts.. that is to say, an INDEX starts. . (The INDEX Function is used to find the appropriate cell value based on co– ordinates for a given Grid (Spreadsheet Range in our case)<o></o>
. The final argument of the Index function, the column reference (co – ordinate), for the Look Up Grid is always 1 in our case. The second argument, the Row reference (co – ordinate) for the Look Up Grid, increases by one for every formula to the right as given by the COLUMNS function which returns an ever increasing number as we go a “&” concatenating (La – Di - dah!!) to the right and extend the column span in its only argument , the Span Range, A:A A:B .. A:C etc. The grid Range is restricted on its horizontal start point by the _ in $B _ $ B$17 column number increasing as the formula is dragged down. The extra IF bit squeezed in here is required to give us an empty cell when in going to the right “a “&” Concatenating” we “overshoot downwards” into the next Unique value in the Look Up Grid. This bit (I think) gives the necessity for the “CSE curly Bracket Stuff”. But my F9 investigation in the Formula block don’t seem to give me an answer on why jet…. (The best I can give for now is a “Rule” rather than an explanation …. “ we have a formula that has a term that does a pair wise evaluation “ . My experiments give an indication that without doing this CSE in this formula , the appropriate Range Reference is lost or rather goes back to the default which appears to be the entire selected rows resulting in this case in extra unwanted entries ) <o></o>
. The final IFERROR is tacked on to take care of the case of us in going ever “ “&” concatenating” to the right eventually resulting in the Column Function returning a Look Up Row for the Index Grid that lies outside its Upper Bound in the vertical direction – so instead of an error we get nothing (which is wot we want, in that case)<o></o>
<o> </o>
<o> </o>
2d) Column C:<o></o>
<o> </o>
Lookingdown column C<o></o>
<o> </o>
{=IFERROR( INDEX( $A$2:$A$17, MATCH( 0, COUNTIF(C$1:$C1, $A$2:$A$17), 0 ),1 ) ,"")}<o></o>
{=IFERROR( INDEX( $A$2:$A$17, MATCH( 0, COUNTIF(C$1:$C2, $A$2:$A$17), 0 ),1 ) ,"")}<o></o>
{=IFERROR( INDEX( $A$2:$A$17, MATCH( 0, COUNTIF(C$1:$C3, $A$2:$A$17), 0 ) ,1 ) ,"")}<o></o>
{=IFERROR( INDEX( $A$2:$A$17, MATCH( 0, COUNTIF(C$1:$C4, $A$2:$A$17), 0 ),1 ) ,"")}<o></o>
…..etc……<o></o>
<o> </o>
. This is / was somewot easier to find and explain. Similar ideas and Formulas are to be found by Googling. So I cobbled a version myself together to give the above, which by trial and error “worked”. My best explanation how and why it “works” is as follows:<o></o>
. Again an INDEX supplies the correct values that we wish to have. The grid (Range) is our Column1 this time and again the column reference (co – ordinate) for the Grid set to 1. The key again is getting the Row co-ordinate. The MATCH function is used within the Index to obtain this co –ordinate which is the Row number obtained the first time that the 0 in the first argument in the MATCH Function matches a zero (0 ) count when COUNTingIF the C Column Array. <o></o>
<o> </o>
. ……………….(The MATCH Function returns a co –ordinate equal to the Row at which the match occurs in the vertical List of its second argument (This list would normally be a given 1 dimensional grid or Range. But here the function COUNTIF has a term that does a pair wise evaluation resulting in an Array which by F9 investigation is seen to be of the vertical form as is required by the MATCH syntax ) - (the last argument 0 or 1 (or True or false) is the usual exactness of The match criteria thing)…………………….. <o></o>
<o> </o>
. The possibilities increase for a count (giving 1 rather than 0 ) as the Formula is dragged down. (By default initially in the first cell there is nothing to count as we have only “Unique Numbers” there!. (- so some care must be taken as to exactly wot one choses to have in the heading!! ). In the second Formula down we have MS001 so will have a count of 1’s until MS002 is reached. In the third down Formula we have Both MS001 and MSOO2 to go through before we hit a 0 count. <o></o>
. The Forth and further formulas down will error because the Match errors as a match is never found. So finally the Last IFERROR bit is tacked on to give an empty cell in such a case.<o></o>
<o> </o>
. Again The “CSE Curly bracket stuff” is required for the “Array Comparison bit”, but I struggle to find any clear definition and explanation of how and why this works. There are some occasional good examples of explanations of how “to go through the motions” / or indications of the “Rules” for when it is required to “do - a - “CSE” ” , for example Post #22 here<o></o>
http://www.mrexcel.com/forum/excel-questions/814156-index-match-return-multiple-values-matching-unique-criteria-3.html<o></o>
.. But I expect maybe no one is privy to the exact mechanism involved in a formula type requiring the necessity of having a CSE Curly Bracket requiring bit????? …………..<o></o>
.. Another good explanation on a similar VBA Thing which I had from an experienced member recently..<o></o>
….

.......
I don't think you should worry too much about how Excel manages............. Down that road lies madness. .......

<o> </o>
<o> </o>
-………………………………………………..<o></o>
<o> </o>
. Sorry to have “Rambled”. - It helped me get it 95% clear in my head , and on the off - chance it may help anyone else I thought I would Post it..<o></o>
<o> </o>
Alan Elston<o></o>

 

[apo]

Nice follow up Doc..

 

[DocAElstein]

Nice follow up Doc..

...Thanks....

.. was just about to write a quick PM to you and one other to mention the Last Posts here in passing as I was not sure if you were still subscribed.

Alan

P.s. I appreciate that you often tack on alternative follow up solutions as well. I allways try to catch them...