Copy cells going down a column with the same patient number to a single row.

Attached is a spreadsheet that has two worksheets (Original and Finished) that has patient numbers listed in column A with Diagnosis codes in column J. What I need to figure out is how to copy or cut the Dx codes for the same patient into first row that lists the patient number. In the worksheet 'Original' I would need to copy or cut J3 and paste into K2 and J4 into L2, J5 into M2 ... The finished information should look like the 'Finished' worksheet. Thank-you for your help in advanced.

I'm getting a Compile error: Syntax error at: Cells(KRowCtr, Cells(KRowCtr, Columns.Count).End(xlToLeft).Column + 1) = _

Cells(ARowCtr, "J")

Hi kschaefer,
. The code from Bailey_Thomas came very close. (That syntax error was obvious: just an extra line there in between. - As it was one code line separated with a _ there must not be any empty line between the two parts of the same code line.

….anyways here is my go. It seems to work. There are a lot of messy green comments on it, mainly for my benefit as I am learning as I go along answering these sort of Threads. But you can easily edit them off and edit other bits as you chose….

Please Login or Register  to view this content.

…… and here is your returned file (Saved on XL2007 as .xlsm ) Macro in Macro Module “Alan”:
https://app.box.com/s/7idndjz9x6wszm85fnw2
.. also attatched..

…………………………………………………………………………………………………………………………………………………..

Hi Bailey_Thomas,
. I work through a lot of people’s codes, learning as I go along. I learnt a new way to get at the unique values with the .RemoveDuplicates, bit. Thanks. I learn the most from Threads where people give different solutions to the same problem. (Amazingly close effort as you were not at your computer!)

Hi again kschaefer,
. I am a bit new in this Forum, Excel Forum, and I am afraid do not quite know my way around. – It just occurred to me that you are in the Excel General Sub Forum and so may not be looking for a VBA solution at all! - Thinking about it you may not have a massive amount of Patients? - so a manual solution might be better, just one to reduce tediously copying every single cell.

. So. To do this. Taking your first set of patients as an example. (I assume you have XL 2007 or above as you sent a .xlsx File)
.1) Highlight Range J2 to J12 (mouse Left Click in Cell J2 and while holding left mouse down drag cursor to cellJ12)
.2) Copy that selected range to Clipboard (Ctrl C or click on the symbol for copying to clipboard above left in the Start(Home maybe in English?) Ribbon)
.3 ) Select cell K2. (Left click mouse in cell K2)
.4 ) Select the pull down options under Insert above left in the Start(Home) ribbon.
.5 ) Choose the option which in XL2007 in English I guess is something like Transpose (XL 2010 has a symbol with two small rectangle boxes and a curved arrow demonstrating a 90 degree rotation).
.6 ) You should now have the First Patients entry looking similar to wot you want.
.7 ) Repeat the above for all patients.
.8 ) Select the entire J Column (Click on small J box above the column)
.9 ) Delete that entire J column (Click on the Delete Symbol above a bit right from center in the Start Ribbon to delete the entire column (not just what is in it))
.10) Re-type in “DX” in first J cell.

Alan
Bavaria

P.s. 1) The above steps (1 – 9) is wot I did as I “cheated” to get part of the code I supplied by recording the above steps ( 1 -9) as I did it using the macro recorder! (This macro recorder produces automatically a crude VBA code based on the actions you are taking as the recorder is switched on.)

P.s. 2) One advantage of my code is that the Patient order can all be jumbled up, and it still works to give you the Output Lines that you want. (There remains then the question of where you want those Output Lines. In this case the format that Bailey_Thomas’s code gives might be appropriate – There the list You want is given alongside The Patient Numbers. My code could be easily modified to do that as well. Let me know if You want to do that and need any help). (My code would put the Output Row somewhere around the average position for mixed up patient Order)

Here's another approach if you could use a formula.

This array formula entered into J2.....committed not as regular formulas but by pressing and holding Ctrl + Shift then hitting Enter.

You'll know it's entered correctly when you see {} curly braces around the formula in the formula bar. You don't type these in yourself...Excel does it for you. Once committed drag-fill down to U23 or as far down as you have data and right until blank cells are all you get.

The file is attached; solution is on Finished2 sheet.

Formula:

Please Login or Register  to view this content.

Here's another, shorter / simpler array formula....committed and filled down and across the same way.

Formula:

Please Login or Register  to view this content.

. Hi Flame

Originally Posted by FlameRetired

Here's another approach if you could use a formula.

This array formula ........

Originally Posted by FlameRetired

Here's another, shorter / simpl...........

.
. I love the Threads where more people give different solutions to the same problem. That is when I learn the most. Array formulas are still a bit of a mystery to me. I am still learning Excel and find VBA easier than formulas, especially those "CSE Curly bracket” things!) (Especially as many Profi's tell me there are no "Array" Formulas in Excel...?. )
. But when I have one doing something similar to wot I have done with VBA it helps me to get my teeth into it. It is late now here, but I will be taking a good look at these formulas tomorrow. Thanks for the extra input.
Alan
Bavaria

(P.s. great to have the attachment - the formula comes up automatically translated. Up to now I have always had to translate them from the English Screen shots…(But it was / is all good practice as well I suppose!)

@Doc.AElstein







@Doc.AElstein

(P.s. great to have the attachment - the formula comes up automatically translated. Up to now I have always had to translate them from the English Screen shots…(But it was / is all good practice as well I suppose!)

If you need...
http://excel-translator.de/

Originally Posted by Indi_Ra

..........

If you need...
http://excel-translator.de/

Super!
. I had been looking for something like that.
. I just tried it. It is not perfect as it does not appear to change the commas for semicolons and vice versa. But it does change the commands, so that is a great help.

Many Thanks
Alan