• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Help Needed

R

rumshar

Member
Hi Excel Gurus,
Good Morning!
I have an excel file(Sheet-Junk Data) where there are many columns which read as:
A1:p1 =
Sl.# Date File Name Comments Physician 1 Physician 1's Fax # Physician 2 Physician 2's Fax # Physician 3 Physician 3's Fax # Physician 4 Physician 4's Fax # Physician 5 Physician 5's Fax # Physician 6 Physician 6's Fax #.
Kindly note that not all rows have all these info. Some columns have only Phyiscian 1 and his fax number where as some have 2 physician's information and so on.
My Requirement:
I want to keep only few columns like:
Sl.#,Date,FileName,Comments,Physician,Fax#
So, if any row has more than 1 phyisician then those data should be listed immediately below phyisician1 and first four columns (A:D) should be repeated for each row.
I am uploading file herewith for your reference. Kindly look into sheet 'Arranged Data' for output. May I request you to help me with VBA solution?

With Regards
Rudra
 

Attachments

  • Spreadsheet.xlsx
    23 KB · Views: 8
dan_l said:
I'm not even saying this is good.
Click to expand...
Hi Dan,
Thanks a lot,
Who says this is not good?...you are right this is not good...this is too good!!!It works like magic!!!!. Could you pls modify your code so that Date also comes in the 'Output' Sheet?Sorry for troubling you again!!!!
I wish I could code like you!!!!
With Regards
Rudra
 
It's not going to get much on the order of style points.

Let me know that this works.
 

Attachments

  • Spreadsheet.xlsm
    43 KB · Views: 4
@rumshar Please note that you should never post real, live or actual data on internet forums. This can be a privacy issue for your employer / customers. Just a suggestion.
 
Please let us know once your question is resolved so that I can remove attachments.
 
r1c1 said:
Please let us know once your question is resolved so that I can remove attachments.
Click to expand...
I have downloaded the attachment(with Code), you may go ahead and remove it. Thank you very much for helping me out.

With Regards
Rudra
 
dan_l said:
It's not going to get much on the order of style points.

Let me know that this works.
Click to expand...
Hi Dan,
All these days this macro worked perfectly fine becuase the maximum number of columns remained unchanged.Now, since the number of columns in this spreadsheet have changed, it is still taking maximum 5 columns only. Could you please modify this macro so that it takes any number of columns? One more thing, in sheet 'Junk Data' Cell C2 (2013-12-09T11_03_59-0600.pdf) has two physicians but it is taking only one physician(to arranged data). Please help me!!!!

Spreadsheet it attached herewith.

Thank you very much
With Regards
Rudra
 

Attachments

  • Spreadsheet Further modification Required.xlsm
    42.6 KB · Views: 6
what's the 6th column?


There's a loop at the end of it that puts the data back into an array so you can output. First line is For i = 2 To coldoctors.Count, change the 2 to a 1.
 
dan_l said:
what's the 6th column?


There's a loop at the end of it that puts the data back into an array so you can output. First line is For i = 2 To coldoctors.Count, change the 2 to a 1.
Click to expand...
Dan,
Good Morning!
As of now this macro can deal with only six physicians. How can we make it work for any number of physicians? With your suggestion I was able to fix the 'Second' problem. Thanks for that.

With Regards
Rudra
 
You must log in or register to reply here.
Back
Top