need to enter data of various patients in each row.each patient variables being entered in different rows.but problem is certain reports have multiple values pertaining to different dates but they are of the same patient. how is it possible to enter different values but for the same individual??
View 9 RepliesAny way to automatically update my spreadsheet align all of the cells in the correct row/column.
Attached is a sample sheet. Columns A-D show how they originally look. Columns F-I show how I would like them to appear.
SAMPLE.xlsx‎
We receive about 20 sales files of several hundred lines of data each day from various agencies. I want to create a macro / VBA code which checks that the data submitted is correct so that we can upload it into our database without import errors and / or having to manually check each line of data.
I envisage something like an output report:
#####################
149 entries
Column A - Date - OK
Column B - Customer_Phone - Errors (Should be 11 digits)
Row 21 - Customer_Phone - Error (Not 11 digits)
Row 108 - Customer_Phone - Error (Contains letters)
Column C - Outcome - OK
Please correct and re-check.
#####################
I need a formula that will look up a cell to get a figure from, but there is three of the same name (sometimes more, depending on different products sold) i.e. "Dept Total" (shown below & attached for easier reading) ....
View 7 Replies View RelatedI have a file which ask for UserId and password. If password is correct, the time stamp should be recorded on the worksheet. However I have some slight probelms on the code I am working on. can someone help me check my coding?
View 14 Replies View RelatedI have one work sheet (worksheet #1) that everyone in the company uses. With this worksheet they submit orders to my department.
When I receive it (worksheet #1) I have a macro built in so that all i have to do is click a button and the sheet get's logged onto a certain workbook (workbook #1) on the appropriated tabbed sheet.
On my log workbook (workbook #1) I have two tabed sheets "2005" and "2007", and all of the worksheet#1's go to the next line on "2007"
On occasion a sales person will open up an old worksheet and the macro will log it onto "2005".
I want to create a macro for (workbook #1) that goes something like this:
If any files attempt to write to sheet "2005"
do not allow or (False)
move line item to sheet "2007" to the next open line.
I have a worksheet that look something like the excel.jpg but hundreds of rolls instead
I wonder if there some way i can convert it into something like exel2.jpg without having to do it manually
if excel is not capable can i use access to do it ...
I have a workbook where employee scheduling is done on a monthly basis in 15 minute intervals. It is laid out like this:
******** ******************** ************************************************************************>Microsoft Excel - Key Support Services 090813.xlsx___Running: 12.0 : OS = (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutB2C2D2E2F2G2H2=ABCDEFGH2Time8/1/20098/2/20098/3/20098/4/20098/5/20098/6/20098/7/200938:00 AM 48:15 AM 58:30 AM 68:45 AM 79:00 AM 89:15 AM 99:30 AM 109:45 AM 1110:00 AM 1210:15 AM 1310:30 AM 1410:45 AM 1511:00 AM Schedule [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name boxPLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
The times go from 8 AM - 8 PM, and all of the cells within the range B3:AF51 are drop downs with the employee names listed. They are conditionally formatted so that we can easily see who is scheduled to work when, and on what days.
The problem that I have is that I like this layout for inputting the data because I can see it all very clearly, but it is not good for archiving or saving past months data. I want to be able to take that view and change the format so that it can easily be summarized in a Pivot Table for all of the historical information.
So, I need to take the date and time and format both of them into a column, put the client name (will be the sheet name) in the next column, and then the name of the staff that worked during that time.
******** ******************** ************************************************************************>Microsoft Excel - Book1___Running: 12.0 : OS = (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutA1=ABCD1DateTimeClientStaff28/1/20098:00 AMClient1Employee138/1/20098:15 AMClient1Employee148/1/20098:30 AMClient1Employee158/1/20098:45 AMClient1Employee168/1/20099:00 AMClient1Employee178/1/20099:15 AMClient1Employee188/1/20099:30 AMClient1Employee198/1/20099:45 AMClient1Employee1108/1/200910:00 AMClient1Employee1118/1/200910:15 AMClient1Employee1128/1/200910:30 AMClient1Employee1138/1/200910:45 AMClient1Employee2148/1/200911:00 AMClient1Employee2158/1/200911:15 AMClient1Employee2168/1/200911:30 AMClient1Employee2178/1/200911:45 AMClient1Employee2188/1/200912:00 PMClient1Employee2198/1/200912:15 PMClient1Employee2Sheet1 [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name boxPLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
From there I can take a completed month, and quickly run some code to make the change, and add it to the historical tracking where it is all summarized.
I have a data feed that gives me a summary of a set of data. I want to be able to work back to what would be the original data (this unfortunately isn't available). The attached excel file as an example of what I am trying to do.
I am not too sure about the working with the dates etc.
I have a worksheet that has similar data but some maybe different lengths. What im trying to do is to be able to have them all in line so where the diagram no. is on the worksheet they are all lined up. On the excel sheet attached I want TURN NO in column J to line up with turn no in Column A each time.
View 2 Replies View RelatedI have a stacking program but it does not keep the first column associated with the data that is stacked as well as the first row. This is how the database looks now:
CREATE TABLES LIKE BELOW?July '07August '07September '07
HR #55551341,929
HR #57217311,653
HR #102322,8981,988
HR #98221,5571,097
this is what I need:
CREATE TABLES LIKE BELOW?HR #55551 July '07
HR #5721 July '07
HR #10232 July '07
HR #9822 July '07
HR #55 34 August '07
HR #57 731 August '07
HR #102 2,898 August '07
HR #98 1,557 August '07
HR #55 1,929 September '07
HR #57 1,653 September '07
HR #102 1,988 September '07
HR #98 1,097 September '07
The current VBA prgram just stacks the columns in one column going from right to left.
I need to change the layout of data within a cells. Attached is a sample of what I need to do
View 9 Replies View RelatedI have a spreadsheet that tracks hours for employes and I need to get the data in a different layout to import to a database i'm building. The example spreadsheet has 2 tabs, the first is the format it's currently in and the second in the one I need it in.
View 4 Replies View RelatedI would like to know if there is a way to change the format of external data? Instead of the default:
the sheet is very long and monitors the sales activities of each individual every day extending vertically down the sheet until the present day.
my first priority was to get the information recorded but now i am wanting to use it.
how would an expert arrange this data to make analysis easier going forward?
ultimately i will be wanting to look at individuals, groups of individuals, certain types of activity etc within various time periods.
Is there an way to make an YES and NO button where when i click YES it pop-up a box asking for confirmation on the action and if you confirm it then the data wrote into A1, A8, D1, D8 will be saved into "Product Exit" worksheet then the data into A1, A8, D1, D8 will be cleared.
aswell as a NO button but the NO button will save into Product not sold IF confirmed TRUE and then cleared and if confirmed FALSE will just clear A1, A8, D1, D8. if it cant be done with the confirmation can it be done without it example ?
I would like to state how fantastic a resource this forum has been to me as a beginner. So keep up the good work! The issue I have is that my spreadsheet contains one column which shares two types of data: Component Type and Tag Number. What I would like the script to do is:
1. create a new column
2. move the Tag Number data from its current column into the newly created column
3. ensure that the data is still on the same row as it was previously
One thing to bear in mind is that the tag numbers consists of various formats (spaces/no spaces), but always begin and end with brackets - "(XTU U-532-934)". I have included and example of the current state of the data and what I would like to look like after.
I would like to change the data layout from the "Original Table" to the "Final Table" as in attached file. Belinda_June-25-07.xls. How do I use VBA to do the change? In my original table, I may have several hundred rows and more than 36 columns for the time periods.
View 3 Replies View RelatedI have a bar chart that monitors month over month activity. I use Layout 5, which allows for the numbers data to neatly be organized in a table below the bar chart. However, i recently started using the data on a secondary axis to accurately reflect trend lines. It works great, but now the data table below displays redundant information. see attachment.
example.jpg
I just want that data in the data table to be displayed once, completely leaving of the axis legend.
I need a macro created, which extracts data from the text file, and displays the low level detail which is on the text file.
I have created a sample of the desired results on a tab called "Low Level Results".
I have already created a macro already which extracts data from the text file, but this gives me a high level view and the results of this is on the tab "High Level Results" ( which you might be able to adapt)
Please find attached two files,
Text File,
And Excel spreadsheet
Rows 1 to 12 of the attached .jpg file shows the format that I receive from a University. However, in order to check enrolments, etc., I would prefer it to be in the format as shown on rows 15 to 19, that is, one complete student record per row.
As you can see, not all students are studying the same number of subjects and the subject mix is also different.
I have a following table:
A B C
1 City Name List
2 NY Peter 11; 23; 12; 11; 14
3 Toronto John 24; 25; 87
How can I, in a separate worksheet, create a following table?
A B C
1 City Name List
2 NY Peter 11
3 NY Peter 23
4 NY Peter 12
5 NY Peter 11
6 NY Peter 14
7 Toronto John 24
8 Toronto John 25
9 Toronto John 87
I'm trying to create a formula that will added the correct amount in the correct cells, I have create a dunny sheet in trying to achieve this. If Cell B8:B11 = ABS or Dum that any points won should be added to Cell L8:L11 right now its adding it into K8:K11. If Cell B8:B11 = is Blank any player points should be added to cells K8:K11. I'm using this formula throughout cells K8:K28 =IF(J8>J25,1,IF(AND(J8<>0,J8=J25),0.5,0)) Any thing in red is incorrect anything in blue is what I'm trying to achieve.
View 4 Replies View RelatedI have responses from a questionnaire in the format below, and need to mail merge to a word doc.
name
subject
teacher
grade
comment
[Code]....
If I'm right I need it in the following format for a mail merge.
name
subject
teacher
grade
comment
[Code]....
I need to get this fixed by 5 pm tonight, otherwise a foresee a night of copy and paste ahead. I thought pivot tables might work, but alas no, it only tells me the number of the comments i have.
My last post here was brilliant, I was very close to figuring the problem out myself (I'm learning) and the help I got here really was useful to help me finally get what i wanted to work.
Unfortunately, this request isn't so simple, as I haven't a clue what to even search the forum for to help me with this.
I have attached a simplified example to try and explain what i would like to happen.
Sheet1 holds information which is regularly updated, so the number of rows can vary, as the number of customers vary.
I want to produce a management report from this data, with a specific layout.
I have put the layout into Sheet2 - the cells highlighted in yellow are there simply as a reference to show the cell from which column would go where.
I produced the top report, by filling the cells with =and then clicking on the cell on sheet1 that I wanted displayed there.
I thought that if I then selected the table and filling down, it would produce the same layout table for all the other rows - but I was wrong.
Another problem is that, this report is needed to be entirely automatic, so I don't want to fettle filling down cells for the number of rows. I will need it to count the number of rows in Sheet1 and produce a report in the specific layout for each company in sheet 2.
It may be an option to use a button create the report, to save the report into a new workbook with automatic file name containing the date and also print a copy out automatically on the default printer.
I don't know how to go about this task at all, so I would really appreciate some help, even if it is pointers for what i should be searching the forum for!
I previously posted a problem related to copying data from one sheet and paste it in another workbook when "Delivered" is chosen from a drop down menu. Everything works fine with my dummy files, but a problem comes up when I try to incorporate this code into the original file.
The issue is that once the code copies the info from the range A3:D3 it has to paste it into different cells in the other workbook.
Please see the attached file to see where I need the data from the range A3:D3 to be pasted.
I am trying to reference data that is currently horizonal in excel and would like to reference that data to get output horizontally with some additions. I have pasted a screenshot of my test spreadsheet. how to do it step by step as I am completely a newbie in excel. As displayed below my data is in Cells A1 thru E3. I want to get output in Column H for this data but with text additions. For eg: in A1 I have Rachel 1/1 however in H1 that should become "My name is Rachel 1/1"....
View 9 Replies View RelatedI have a form on which I select a date (using a calendar form) and on the form it works perfectly. The date is in the correct dd/mm/yyyy format for example (03/04/2013 - 3rd April 2013), I then insert this date into a worksheet and it comes out in the correct format, but it is actually turning the dates around so where it should be putting 3rd of April (03/04/2013), it's inserting 4th March (04/03/2013) instead. It looks very much like the form sees the date in dd/mm/yyyy format and the worksheet when taking the data from the form thinks it in US format (mm/dd/yyyy).
[Code] .....
I have a table with 3 columns of dates and then a column with Set # that I
feel in the box #.
I need to see how many items processed for each set per day.
Example:
[url]
The problem is that it counts the correct amount but not with the correct
dates.
The formula that I use is:
=SUMPRODUCT(--($I$3:$I$8<>"")*(($C$3:$C$8=39601)+AND($E$3:$E$8=39601)+AND($G$3:$G$8=39601)))
I need identifying correct title and corresponding company name. For this I need a UDF which looks for first occurrence of title having event or marketing or meeting word in data and if found the remove other titles and company names already separated with "/".
Sample Data
Producer, Target BTC@Periscope/Events Manager, Recognition Events@Minneapolis Park & Recreation Board/Event Producer@Events by JLS
Required Result
Events Manager, Recognition Events@Minneapolis Park & Recreation Board
Sample Data 2
Sales Associate@Teavana/Event Assistant@City of Saint Paul
Required Result 2
Event Assistant@City of Saint Paul
Sample Data 3
Sales and Catering Manager@Bunker Hills Golf Course at Town & Country Caterers/Marketing Coordinator@Town & Country Caterers
Required Result 3
Marketing Coordinator@Town & Country Caterers