Move Excel Informtion to Columns Side by Side by Patient

[bpxmas]

I have a report I exported from Crystal v10 into Excel.

There are two columns A and B in Excel with ongoing data. I need to separate the data by Patient and place them in the next two columns C and D, etc. As you can see below the column names are 'Patient' and 'Patient Detail'

Col A Col B
Patient Patient Detail
John Smith Address

Col C Col D
Patient Patient Detail
Tom Jones Address

etc.....

I need the information to be side by side. Hopefully this makes sense. I tried creating Pivot Table, but it doesn't work and it rearranges the data into ascending alpa. Hopefully, someone can help me.

Thanks in advance.

 

[lameid]

I'm not following side by side... To me it is side by side or two column groupings. Do you want it to run Vertically instead of horizontally like a table format?

 

[SkipVought]

hi,

Please COPY several rows and columns A-C and post back.

What you state is not at all clear.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[bpxmas]

Hope the formatting below describes what I want to accomplish. Horizontal side by side.

I want each new 'Patient' instead of appending to the same Col A & Col B input of each new Patient goes to the next to columns. Patient 2 Col C & Col D, Patient 3 Col E & F, etc. There are about 50 Patients and I want all their information in Excel to be next to each other instead of one long column. There are only two Columns for each Patient the first column are the names of the fields being populated with information in Patient Info.

Col A Col B Col C Col D
Patient1 Patient1 Info Patient2 PatientInfo

Company Name Houston Company Name Honeywell
Patient Jones Patient James
First Name Jeffrey First Name Rod
Middle Name
Last Name
Address 1 2291
Address 2
City
State Abbrev CA
Postal Code
Country Abbrev US
Date of Birth
SSN
DL
Home Phone 1
Home Phone 2
Other Phone
Employer Name
Company Name Houston
Patient Kay
First Name
Middle Name
Last Name
Address 1 2291
Address 2
City
State Abbrev CA
Postal Code
Country Abbrev US
Date of Birth
SSN
DL
Home Phone 1
Home Phone 2
Other Phone
Employer Name

Thanks in advance for your help.

 

[lionelhill]

Here's one suggestion, probably far from the best.

As I understand it, your data are in cols A and B. The blocks of data all start with a patient number such as Patient1. You want the blocks separated out into cols C onwards.

(1) arrange that C1, E1, etc. contain Patient1, Patient2 etc. There are various ways to do this, post back if you get stuck. It won't matter if D1 = C1 = Patient1.

(2) Name column A "allnames" and column B "alldata" if you want. I did this for the bits below.

(3) Put in C2
=IF(C1<>"", MATCH(C1, allnames, 0), B2)
This gives the starting address of the block.

(4) Put in C3
=IF(C1<>"", E2,B3)
This gives the end address of the block.

(5) Copy these right-wards to the end of the table for Patient 50, and sort out the fact that the last patient won't have an end because there is no Patient51.

(6) Put in C4
=IF(ROW(C4)-4 < C$3-C$2, INDEX(alldata, C$2+ROW(C4)-4, MOD(COLUMN(B4), 2)+1), "")
And copy this across to the end, and down as far as the maximum number of bits of information you have for any patient. It doesn't matter if some have more information than others.

This should fill out each patient with all lines of data associated with them, together with the names of the bits of data. It will, unfortunately, replace blank entries with "0", so you may want to think further to tidy this up, if you have many blanks.

Hope it works for you; it did for me.

 

[SkipVought]

Hi,

First important question:

What is the CURRENT STRUCTURE of your data?

Please post a sample of THAT.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[bpxmas]

I'm sorry I have a question do you want me to name the the first column 'allname' and the second 'alldata'? That's what I did see below.

3) I put the formula above in C2

A B C
allnames alldata
Company Name Houston formula(C2)
Patient Jones, Thomas A formula(C3)
First Name Thomas formula(C4) I get #VALUE
Middle Name A
Last Name Jones

When I copy to the right formula C2 it gives me the same Patient's Name doesn't get the new Patient name? I'm sure I must be doing something wrong. Thanks for your help and being so patient.

 

[lionelhill]

sorry, I should have made things clear. When I referred to names, I meant "named ranges". This is just a method for when you don't want to type $A$1:$A$123 to refer to a range of cells A1:A123 in formulae that will be copied.

Instead you can select the cells (e.g. the whole of column A, or the whole of columns A and B), and type a name (e.g. allnames) in the small white box just to the top left of the spreadsheet area. This then names that area of cells so you can refer to them by name in formulae.

The formulae I gave you won't work unless you either name ranges, or unless you substitute the named ranges by $A:$A for "allnames", or $A:$B for alldata.

Good luck!

 

[bpxmas]

I'm still having problems.

Let me tell you exactly what are in the columns.

Column A has all fields Column B detail of Patient
Company Name Houston
Patient Jones, Tom
First Name Tom
Middle Name
Last Name Jones
Address 1 3375 Mayflower
Address 2
City Plano
State Abbreviation TX
Postal Code 33476
Country Abbreviation US
Date of Birth 01/04/1975
Social Security Number 333-33-3333
Drivers License
Home Phone 1
Home Phone 2
Other Phone
Employer Name
Employer Address 1
Employer Address 2
Employer City
Employer State Abbrev
Employer Postal Code
etc

Then it starts all over with another Patient which needs to have all the table fields below. And, their detail as well

Column A has all fields Column B detail of Patient
Company Name Houston
Patient Smith, Janet
First Name Janet
Middle Name L
Last Name Smith
Address 1 2211 Sandy Lane
Address 2
City Henderson
State Abbreviation NV
Postal Code 1234
Country Abbreviation US
Date of Birth 12/12/1982
Social Security Number 111-22-2120
Drivers License
Home Phone 1
Home Phone 2
Other Phone
Employer Name
Employer Address 1
Employer Address 2
Employer City
Employer State Abbrev
Employer Postal Code
etc

I need each Patient to append to columns so they will be in their own columns instead of one long column. Instead of cut & past Patient2 to C & D - Patient3 to E & F that would take forever.

I really hate to be a pain, but I'm sure you can help. Thanks again for your expertise.

 

[SkipVought]

This DREDFUL format is NOTHING that can be manipulated very well using native Excel features.

As I stated previously, your FIRST objective sould be to either FIND or RESTRUCTURE your source data into a Table Structure.

From there, it will take VBA to do what you're looking for. Are you up to coding? Forum707.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[bpxmas]

Okay, thanks for your time and expertise.

 

[xlhelp]

If all your data is in sheet1, then on Sheet2 Row 1 put "Company Name" a1, Patient in B1, First Name in C1 etc all the way till all your field names have been filled. (We will assume this goes all the way to Column Z)

In cell A2 type the following:
=IF(LEFT(Sheet1!$A1,12)="Company Name",OFFSET(Sheet1!$B1,COLUMN(A1)-1,0,1,1),"")
Copy the formula all thw way from A2 to Z2

Highllight A2:Z2, use fill handle to copy down as far as the last row in in your Sheet1 (don't forget we are still on Sheet2)

Sort Data. Delete all blank rows.

You will get Zeros in some cells (you can always find & replace them); but c'est la vie

Canadian eh! Check out the new social forum Tek-Tips in Canada.
With the state of the world today, monkeys should get grossly insulted when humans claim to be their decendents

 

[xlhelp]

Ooops, forgot. Don't forget to remove the formulas by highlighting all data in Sheet2; then do Copy, Paste Special, Values before sorting your data.

Canadian eh! Check out the new social forum Tek-Tips in Canada.
With the state of the world today, monkeys should get grossly insulted when humans claim to be their decendents

 

[bpxmas]

I'm in Sheet 2 and when I put the formula in A2.....it says FALSE

I need my field (table) names to go down not across
A1 Company
A2 Patient Name
A3 First Name
etc

Thanks.......

 

[xlhelp]

It's too bad that we don't exactly know the set up of your spreadsheet. My formula assues that "Company name" title is in cell A1. You would either need to adjust your data in Sheet1 or adjust the formula to suit what's on your worksheet.

Also, if you did what I suggested, then once you are done you can always transpose the data the way you want.

If you are just pluggin in a formula without actually seeing whether it fits within your constraints; you are wasting valuable time for yourself and for those who are trying to help you.


Canadian eh! Check out the new social forum Tek-Tips in Canada.
With the state of the world today, monkeys should get grossly insulted when humans claim to be their decendents

 

[SkipVought]

It does no good for YOU if you just state...

when I put the formula in A2.....it says FALSE

We need to know EXACTLY WHAT FORMULA you have in A2, because the 'sympoms' stated, indicate that you did NOT enter a proper IF() formula.


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[bpxmas]

When I do an 'Export' from Crystal Reports v10 all my data is in two columns - it's continuous. What I'm trying to accomplish is after the last field for the first Patient I want to be able to use a formula to cut the field names & detail for Patient2 to Column C & D. Does this make sense.

1st Column Field Names
2nd Column Detail Data

I'm sorry. Company Name is in cell A1 see below

TABLE FIELDS DETAIL
COLUMN A COLUMN B
Company Name A1 Houston B1
Patient A2 Adams, Tom B2
First Name A3 Tom B3
Middle Name A4 B4
Last Name A5 Adams B5
Address 1 A6 2234 Monson B6
Address 2 A7 B7
City A8 Cedar B8
State Abbreviation A9 TX B9
Postal Code A10 34324 B10
Country Abbreviation A11
Date of Birth A12
Social Security Number A13
Drivers License A14
Home Phone 1 A15

Then Col C would have the same table fields like Col A but Col D would have new Patient information for Patient2 clear through Patient???? So one Patient would have two columns -1st one for Field Name - 2nd one for Patient Detail.

Hopefully the above is helpful. Thanks again.

 

[SkipVought]

When I do an 'Export' from Crystal Reports v10 all my data is in two columns - it's continuous...

We found the problem!

You need a tablular export rather than the terrible one that you are stuck with.

With a tabluar report from CR, you could have a solution pretty quick and clean, IMHO.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[lameid]

If you have any developement people - CR also uses queries to display data. You could possibly have something setup to dump the results of the underlying query to a text file. Depending on what CR is doing will determine if the data is exactly what you expect (the data will be there but sometimes reports do grouping, filter or other things after the query). If you have the expertise in house, it should be a quick thing... They can just schedule a job to export the data periodically (i.e. every day at 7:00a or Mondays at 4:00a).

Of course they may just give you a report in a different layout... I've barely used CR myself. You might have an option to export the data as opposed to the report.

 

[bpxmas]

These are the options for exporting in CR:

Acrobat
Crystal Reports
HTML 3.2
HTML 4.0
MS Excel 97-2000
Ms Excel 97-200- (data Only)
MS Word
ODBC
Record Style (columns no spaces)
Record Style (columns with spaces)
Report Definition
Rich Text Format
Separated Values (CSV)
Tab Separated-Text
Text
XML