I have a large patient data set consisting of one column that includes their zip codes and one column that includes the number of times they were seen at a hospital over a period of time. I want to try to consolidate the data so I just have the number of total cases (for all patients) in a certain zip code over that time period. There are several thousand patients and it would be great to avoid doing it manually.
View 1 RepliesI have data that looks like this.
A1 B1 C1
1 2 100
2 3 100
3 4 100
My result should look like '1' in column A1, '4' in column B1 and '100' in column C1. I have hundreds of rows this way and any help will speed up my process. I tried writing a macro, but have no idea on how to delete the rows in between. Even if we can copy the result to the next column (i.e. have '1' in column D1, '4' in column E1, and '100' in column F1, that will solve the problem.
way to combine multiple rows into a single row using vba?
I have large amount of data approximately 5000 rows. I would like to combine all of the rows by DOB.
The reason why they are listed multiple times if they have multiple Benefit #'s.
Here is an example of the multiple rows of data: ....
I have a zipcode file I am trying to consolidated that has zipcodes and customers. how to consolidate the customers into a single row by zipcode as outlined below
Current Data
Zipcode Customer
00001 Customer 1
00001 Customer 2
00001 Customer 3
00002 Customer 4
00002 Customer 5
Desired output
00001 Customer1, Customer2, Customer3
00002 Customer4, Customer5
etc etc
I am working with an excel file that shows daily whether an individual is checked into a shelter. The dates are often consecutive. I am trying to merge rows to reflect a true length of stay, not daily. here is what my data looks like:
First Last SSN DOBEntry Date Exit Date
JaneDoe1111111111/11/11114/1/20094/1/2009
JaneDoe1111111111/11/11114/2/20094/2/2009
JaneDoe1111111111/11/11114/3/20094/3/2009
JaneDoe1111111111/11/11114/4/20094/4/2009
JaneDoe1111111111/11/11114/5/20094/5/2009
JaneDoe1111111111/11/11114/6/20094/6/2009
JaneDoe1111111111/11/11114/7/20094/7/2009
JaneDoe1111111111/11/11114/8/20094/8/2009
JaneDoe1111111111/11/11114/9/20094/9/2009
JaneDoe1111111111/11/11114/10/20094/10/2009
JaneDoe1111111111/11/11114/12/20094/12/2009
JaneDoe1111111111/11/11114/13/20094/13/2009
JaneDoe1111111111/11/11114/14/20094/14/2009
JaneDoe1111111111/11/11114/15/20094/15/2009
JaneDoe1111111111/11/11114/16/20094/19/2009
JaneDoe1111111111/11/11114/21/20094/22/2009
Here is what I would like it to look like:
First Last SSN DOB Entry Date Exit Date
JaneDoe1111111111/11/11114/1/20094/10/2009
JaneDoe1111111111/11/11114/12/20094/19/2009
JaneDoe1111111111/11/11114/21/20094/22/2009
Does anyone have any suggestions on how I can accomplish this with a macro or otherwise so I don't have to manually go through a year's worth of data? Thanks for your help and time.
I am looking for a macro that is capable of deleting all rows in a dataset where the following statements are not true: column B is equal to "OP00" (o, p zero zero), the left hand character of C is equal to "L" and D is equal to "CC", as in the scenario below. I basically need to keep all rows which match the structure below, i.e. to clean the data.
B C D
OP00LKAOCC
OP00LMRPCC
OP00LVOFCC
OP00LVOFCC
OP00LVOFCC
OP00LVOFCC
I am trying to consolidate rows of data in to a single row at Cust Ref level (column A) but I need to ensure the numbers in each monthly column are added up accordingly so I have one total per month by Cust Ref. I hope what I have said makes sense. I have attached the spreadsheet so you can see what I am talking about.
View 6 Replies View RelatedI am trying to compile a spreadsheet for my job that will find an average of all the break times and meal times that all the employees take. I have the employee list and their respective timeclock punches for the week, so I took that and found their meal and break times for each individual day, but I'm having trouble when it comes to consolidating and averaging the data. The first column lists the employees, but their names are repeated depending on how many times they punched the clock that week, and since not every punch was a meal or break, my time columns contain a lot of zeroes that aren't needed. I need a way to just average each employees' meal and break times.
View 9 Replies View RelatedRearrange dataset from columns to rows
However, the solutions do not work if "species " are text instead of numbers:
Parcel
Species1
Species2
Species3
[Code]...
I have attached two documents. One is called "Zone Destination" which is a template I designed. The other file is called "Schedule5_4" which gets downloaded from a work server once a week that contains all employees and their shifts for the entire week. What I am able to do so far is extracting the first row using index and match but I don't know how to extract any of the rows that follow. an employee might have several rows for one day based on a lunch or if they are working in multiple zones during their shift.
Zone Destination File -- start tab has the template in place that gets copied over when you create a new tab.
-- employeeroster tab contains the employee roster that i use to match with the schedule5_4 file. i changed the names and also reduced the amount to make it easier to read. i have over 80 employees but for this example, i only made up a handful.
schedule5_4 File -- this file contains all the data that I need to pull from. The criteria that I am using is by employee and date. I'm matching from the employee roster tab and also the date in cell a1 located in the zone destination file.
[URL]
I'm trying to count the number of rows in a dataset where column A is something, B is something, C is something, D is not something, D is not something else, and column E > 0 OR F is something but I just can't get the result I want.
My actual dataset is a staggering 294,000 rows and 46 columns so here's a simplified version:
I couldn't figure out how to change this function to allow for weekends.
Function to count consecutive dates as one occasion
=--((NETWORKDAYS(SUM(c1)*(B1=B2),c2)-1)>1)
Column A Column B Column C Column D ( I would then use this column in a pivot table to calculate)
pat name pat id date of service # Admissions
[code]....
I am trying to write a macro to transfer patient personal data onto a database.
On sheet 1 the user inputs the patient surname, forename, date of birth, date of test and the test results.
On sheet 2 is the database, where patient surname, forename and date of birth is stored in columns A, B and C respectively. The remainder of the columns on each row is available for scoring test results.
What I would like the macro to do is as follows:
•Read the patient surname as input on sheet 1
•Check on sheet 2 if that surname already exists
•If it does, check if the forename and date of birth also match to sheet 1 for that particular entry.
•If a match is found then copy the test results into a column on that particular row. E.g. could be copied into column D.
•If no match is found, then all the data is entered as a new row on the database (I’ve written the code for this bit but thought I should add it in for clarity)
This will then allow all test results for a particular patient to be read off the same row in the database.
I am having trouble finding an efficient way to expand a set of data that I have by adding another column to it.
Look at attachment : help.xlsx‎
I can do it manually but I have 5000+ rows of data that I need to selectively expand do accommodate the data from the new data set.
I have a sheet with the following information in rows:
PO# Acct# Item# QTY
There are multiple lines that share the same acct#.
How can I create the sheet so that each row combines the records for the same acct#.
For example:
PO# Acct# Item# Qty PO# Item# Qty PO# Item#
There are never more than 6 rows that share the same acct#.
I have written a macro to consolidate multiple sheets into one sheet. I have two sheets which are to be copied one below the other.I am attaching the code for this here.
ub Bingo()
Dim ws As Worksheet, n As Long, flg As Boolean, last As Long
On Error Resume Next
Application.DisplayAlerts = False
Sheets("Consolidated-Input").Delete
Application.DisplayAlerts = True
On Error GoTo 0
Sheets.Add(before:=Sheets(1)).Name = "Consolidated-Input"
n = 3
For Each ws In Worksheets
If ws.Name "Consolidated-Input" And ws.Visible And ws.Name "DCS-User" And ws.Name "Cal" And ws.Name "DCS" = True Then
If Not flg Then
ws.Range("a:c").copy
Sheets("Consolidated-Input").Range("a1").PasteSpecial xlPasteValues
Sheets("Consolidated-Input").Range("a1").PasteSpecial xlPasteFormats
flg = True...................................
I have 85 workbooks in the same folder with a sheet in each workbook called "Budgets". Its the same template in each workbook with different data. I need to create a Summary master file of all my budgets so in the end I will have 1 Workbook with the 85 copies of the "Budgets" Worksheets. Does anyone know the VBA for this?
View 9 Replies View Relatedconsolidate data from different workbooks into a single work book.
Each workbook contains one month payment information for employees.
i want to consolidate the the workbooks into one mastersheet such that i will have twelve columns (One for each month). On the consolidated sheet, i want each contributors monthly contribution displayed under the months to which the cointribution relates.
*find attached a dummy data illustrating the request*
i will like the results displayed as illustrated in the consolidate tab.
I have a clock machine report weekly which generates a spreadsheet very similar to the example attached.
The number of sheets can vary, the number of rows per employee can vary all of which makes me think that I can't use a formula to collate the data. how I can do this?
Tried the consolidate feature, however not sure that it is what I am looking for.I am basically trying to condense several calendars ( one calendar per department in a company) into one Calendar..Each calendar has its own tab and looks exactly the same. (tab names: Clubhouse, spa, and final calendar... possibly more departments added later) Hard to explaing so I will attach the file. All I would like to do is combine all calendars into the "final calendar" and in time order if possible... example below
{Clubhouse Tab} A3 will have the time (10 am) , B3 will have the item "Meeting"
{Clubhouse Tab} A4 will have the time (5 pm) , B4 will have the item "Dinner"
[code]....
I have two spreadsheets of part numbers, one with a full list of parts approx 3500 lines and another with the same part numbers (but only approx 1000 lines) but this file also contains sales history. columns shown below
PART NUMBER, PART DESCRIPTION, RETAIL, COST, UNIT OF ISSUE, CURRENT STOCK, YTD SALES, MTD SALES
What i want to do is merge the two files together so the 1000 parts i have sales history for correspond with the same 3500 lines on the other master spreadsheet, so i can then sort them in which ever order i need at the time.
I have 4 worksheets where the structure is exactly the same except the figures differ
The worksheet have text and values in columns A to S. I need to write VBA code that will combine the data and show the descritions in a consolidated woorksheet.
I have a workbook with multiple tabs (sheets) I would like to make another tab to consolidate all the data, rows and columns into one master sheet.
View 9 Replies View RelatedI have several workbooks (around 15) in one folder which all contain the same worksheets but with different data. The worksheets all have the same columns. What I would like is a way to consolidate each of the worksheets of the workbooks into one new workbook by running the code whenever I want it.
In other words I have workbook 1 till 9 which all have worksheet 1 till 3. And I would like to consolidate them into a new workbook with sheet 1 till showing all of the data.
I have a 2 worksheets (IJOFTD3D(1) and IJOFTD3D(2)) that I need to filter using criteria in 2 columns and combine to one worksheet (GKA Data). These are not the only worksheets in the workbook.
Here is the criteria: Column A (sortcode) = GKA
Column D (Cust_Code) does not begin with I
I am able to get the first worksheet into GKA Data, but I don't know how to get the second worksheet to append to the first using VBA. This is a daily report that I am trying to make as automated as possible.
I have two tables that pull data from two different SQL tables via an ODBC connection and refresh every 5 minutes. The table headers etc are all identical (it is support call information FYI) but what I want to do is display those tables as one as opposed to two seperate tables - can this be done relatively easily and hopefully without the use of VBA? (Unless anyone is willing to provide the code of course!!)
View 9 Replies View RelatedI am trying to automatically copy and paste data from multiple source workbooks into one master file, and could use some assistance with building a macro to do so. Specifically, I'm trying to accomplish the following:
1. Open source file.
2. Within the source file, find any worksheets whose name begins with a 5-digit number.
3. For these worksheets only, copy values from a specified range (B15:B64, J15:J64, and V15:V64), and paste these values into the master workbook (into worksheets already existing in the master workbook, whose names match those in the source workbook).
4. Close the source file and repeat this process for the next one.
In total, I'll have ~20 source files to process in this manner. I have the file names listed in Column C of the "Import" sheet on my master workbook.
So far, I have been successful in taking care of the step 1 (opening the external files). I'm a macro newbie, though, and am not sure how to tackle the conditional cutting and pasting outlined above.
I am trying to make a program that opens a file from my hardrive, adds to a list box and allows me to send to a second box. I then want to be able to open the selected files in box 2. I have done the above except being able to open the files in the box. Below is my code can anyone help. I want all the selected files to open in one workbook, so want all files sheets to be copied into one new workbook.
Private Sub CommandButton1_Click()
If Lb1.ListIndex >= 0 Then
Lb2.AddItem Lb1.Text
Lb1.RemoveItem Lb1.ListIndex
End If
End Sub
Private Sub CommandButton2_Click()
Do While Lb1.ListCount > 0 .........
I am creating a consolidation worksheet. It contains a macro that automatically copy data from other sources and paste it into my DB sheet. It already works, but I want to improve it. The source worksheet databases has been defined with the name “SOURCE”. Is there any way that I can make my macro look for this dynamic ranges directly, copy and paste it into my DB sheet. I am attaching an example of what my current macro is doing.
View 2 Replies View RelatedI have a folder that contains around 45 excel files, each of these files contain a tab called "corp val". (these files change name month to month e.g. Report June, Report July etc)
I am finding on a monthly basis i need to go into each of these excel files and copy the data from the "corp val" tab into a summarized spreadsheet - which i then use the data for various things.
Just wondered if there was a solution to this or will it always be difficult to copy and pasting all the data i need. I am not in anyway an advanced excel user, I am guessing this might be some form of macro in which case I am maybe better to keep doing it the way I was as I am not literate in macros.