Matching Cases And Data?
Oct 14, 2013
I have data collected at 2 time points. Participants have a unique alphanumeric ID that is the same at both times. I need to merge the data into one sheet where each case has all of the information from time 1 and time 2. There are many more cases at Time 1 (nearly 2000) than at time 2 (around 340).
I have prepared an example of what I need (see below), but have lots (around 300 at each time point) more variables than what has been put in the sheet.
Example.xlsx
View 6 Replies
ADVERTISEMENT
Jan 20, 2013
My task is to combine two large databases into one spreadsheet by extending the number of columns. The data from each database is 90% matching based on an identification number, however occasionally there are additional rows or unmatched identification numbers that need to be kept for analysis.
When this happens, there needs to be a blank row inserted to represent the missing data in the rest of the corresponding row.
I am having trouble finding a quick way to do this because I have approximately 12,000 rows (and columns up to DV when combined).
for example:
p1
data
data
data
data
p2
data
data
data
data
[Code] .....
needs to become:
p1
data
data
data
data
[Code] .....
I am guessing I will need a macro of sorts, So far I have made one column that tell me if the ID's are matching or not (1 or 0) and if they are not matching (0) I manually insert the rest of the row that is missing or make space for the duplicate data (which needs to remain).
View 5 Replies
View Related
May 27, 2009
I've spent some time looking through previous threads and found some help, but can't quite get what I need.
I am just trying to look at the age of open cases in a report. I want to know in hours (and maybe minutes) not including weekends. I have the created date and time in J2 and "Now" date and time in K2.
I've used the following that I got from another thread: =NETWORKDAYS(J2,K2-1+MOD(K2,1)-MOD(J2,1))
But it seems to be only giving me hours in 24 hour increments. For example a case raised on 22/5/09 gives a time of 72.00 this morning.
View 14 Replies
View Related
Mar 24, 2009
I am writing VBA code to do the following:
IF A1 > 50 then print array C1:E7
I have this code and it works.
View 14 Replies
View Related
Mar 26, 2014
I have an excel file that lists individuals who came into the clinic on a given day. I am interested in figuring out the amount of unique individuals in a given month/overall. As this is a working datasheet, I would like a formula that would automatically update this information for me. Here is an example of the file that I am working with: unique case example.xlsx
Specifically, I want an IF function that could place an 'x' in column E depending on if the name in column C is a unique instance. But if the name appears several times in the list, I would also like this function to place an 'x' in column E next to the last occurring unique instance. For example, the name 'Hanna D' occurs 3 times in the spreadsheet I have attached, but I want the 'x' to be next to the most recent incident, when she was screened, as opposed to when she was not contacted (missed/doc forgot).
I am not sure if you can even define a unique case in the IF function, let alone the last occurring unique case.
I am not interested in conditional formatting (highlighting duplicate cases and then placing an 'x' next to the most recent highlighted case) because my spreadsheet has 300+ individuals and it is very time consuming to manually move the 'x' when the same individual appears at a later time.
View 4 Replies
View Related
Feb 27, 2014
I have two tables, Table1 one has only customer codes in it, and I have Table2 with plenty of customer codes and those customer name, surname, age, location...
And I want to match and copy each of those customer codes in Table 1 all the information which is on Table2
Table 1:
50025
50026
50086
Table 2: Considering that name, age and location is each in separate cell
50025JohnSecond25Location 1
60085EmilyThird 20Location 2
45454Wilhelm Fourth35Location 3
10000RoseFifth 60Location 4
50086JohnySixth 45Location 5
65501JacobSeventh18Location 6
50026Jackie Eighth22Location 7
And the outcame should be that it finds the value from Table1 in Table2 and copy's information in Table 1 like this:
50025 JohnSecond25Location 1
50026 Jackie Eighth22Location 7
50086 Johny Sixth 45Location 5
View 2 Replies
View Related
Feb 20, 2014
I have two worksheet "ABX" and "ACX" from which I want to find the matching data for specific columns B, D and E only.
Matching data will be in sheet "Match" and vice versa.
Data are present dynamically.
View 1 Replies
View Related
Nov 29, 2009
I have one excel file. I want calculate no of days in coloum ( L16 & L17)
If Coloum C3 is 400 & C4 is NORMAL then automatically calculate no of days in L16 & L17 as per J16, J17 (hrs) & K16, K17 (running). we highlighted in yellow. refer attach file for ur ready ref.
View 2 Replies
View Related
Feb 25, 2014
I have to examine a random number of cases every week. I used to have a little app that had a spot to ask me how many random numbers I need. So lets say this week I need to examine 15 cases, I'd enter in 15. Then it would ask me a range for the random numbers. So, lets say there are 250 cases, so I'd type in 250. Then it would spit 15 unique random numbers in sequential order. So, then I could look at me list of 250 case and review those 15 that the random number generator spit out.
Well, we just upgraded our computers to Win 7 and that little app does not work anymore. I've been playing around in Excel, and I've gotten pretty close to getting something thrown together, but I can't get it to work just right...
View 6 Replies
View Related
Aug 28, 2008
One has the master data in it ( Item #Lot#/Cases per pallet/date ) = 30K rows
Second is the list of item #'s .
I need to find the MODE of cases/pallet by item number.
View 9 Replies
View Related
Nov 8, 2012
I'm using excel 2010, WindowsXP.
I'm trying to create a table which coverts eaches to cases and cases to eaches depending which value is entered.
For example, if the warehouse associate fills in the EACH column, with 24, the CASE column will show 2 (using 12ea/case). If instead the warehouse associate fills in 2 in the CASE column, the EACH column will show 24. The idea is that the associate can fill in either value and it will convert. The problem is that I'm getting a circular reference error.
A1
B1
EACH
CASE
24
2
=B1*12
=A1/2
View 7 Replies
View Related
May 5, 2009
Now that the calculations are working, with the press of a button, I need to be able to select a range of dates and copy all lines within the range to a seperate sheet with the desired name under the same headings they currently reside under. I have included some modified code that is being used in another spreadsheet that was created for me, but I do not pretent to understand all of it and I no longer work with the creator of the spreadsheet. How do I use a button to open the form for date selections and entering the name of the new sheet, and then use the start button on the form to begin the matching and copying to a new sheet? If there is an easier way I am all for that too.
View 14 Replies
View Related
Apr 18, 2013
So I have this problem in excel with comparing 2 columns.
Basically, I have 2 columns(a &B) that I need to compare with one another and find out the matching data. I am trying to use that matching data to enter in our reports.
Both columns might have duplicate items because I am dealing with premium numbers.
Formula to compare these 2 columns and find the matching data and extract it to a separate column. Keep in mind, i am dealing with almost 20,000 lines of data.
View 2 Replies
View Related
Jul 15, 2009
I want the script to find if the value entered in the form is matching the values in column 'A' in the database and if it matches then it needs to select the cell as active cell - to populate the form details. And if there is no matching value found, the script needs to select the last empty cell of the column 'A' to populate the data entered in the form.
1) Form has 10 different fields that needs to be filled by the user.
2) Field 1 - is a text box for 'Request #' to be entered by the user.
3) After filling in all the fields - once clicked on OK, the form should search for the the 'request #' entered on the form in the database (Form and the database are in the same workbook).
4) If the 'Reqeust #' in the Column 'A' matches the the 'Request #' entered in the form, then the matching cell should be selected (Activecell -Were the data can be overwritten, with the new entry)
5)If there is no matching 'Request #' found in the database, the script should loop to select the next available blank cell in column 'A'. So that the form data can be entered.
View 14 Replies
View Related
Apr 24, 2009
Rylo thanks for your help on the other project. Here is step two for the same project.
Basically I'm going to list a step by step list of what needs to be done then attach some sample sets so you can see what I'm talking about.
Step 1 ........
View 12 Replies
View Related
Jul 18, 2006
I need to match up the columns of 1 spreadsheet with those of another. I have a partial address that only lists city and state in one workbook. I need to match these partial addresses up with the master list that contains full addresses. It seems like it should be an easy thing to do, but I can't figure it out.
View 9 Replies
View Related
Nov 19, 2009
programming 2 macros in a bank reconciliation sheet I am trying to create.
Basically the data consists of two sets: the ledger side and bank side. Both sides consists of multiple columns that include date, description and amount.
The following two situations can occur and which need to be covered with a macro.
Many to one matching
When we make payments to say 100 different suppliers at once, the ledger side will show 100 lines with the different amounts. However on the bank side it will only show one line with a total amount for the transaction. As the description on the ledger side is the same for all transactions done at the same time, it should be possible to have a macro add these lines and compare the total with entries on the bank side. Once a matching amount has been found, the macro should place an ‘x’ next to all entries on the ledger and bank side, in order to show that these transactions have been reconciled.
Partial cell matching
In this case we are only dealing with one line on both the ledger and bank side. The issue is that the description does not always perfectly match with one another. The ledger side might say ‘Brown Corp.’ and the bank side might say ‘Brown Corporation’. I want to come up with a macro that can recognize this partial match and still reconcile the lines by placing a ‘x’ to both the ledger and bank entry. The match however should be done with a combination of description, date and amount as several different bookings might be done for the same supplier in the same month.
View 12 Replies
View Related
Feb 15, 2010
I have a report that shows "users" in column A, "time of call" in column B and "Call direction" in column C as below
Usertime of callCall DirectionUser 112/02/2010 09:00:59InboundUser 112/02/2010 09:10:15InboundUser 112/02/2010 09:26:03InboundUser 112/02/2010 09:41:40OutboundUser 112/02/2010 09:52:11OutboundUser 112/02/2010 10:25:28InboundUser 112/02/2010 10:25:32Outbound
On a second sheet I have another report that shows the "user" in A, the "time" in B, the "Status" in C and the "Duration in status" in D. As follows
UsertimeStatusDurationUser 109:28:57Follow Up:00:08User 109:29:05Available, No ACD:04:17User 109:33:22Outbound Working01:08:37User 110:41:59On Break:02:55User 110:44:54Outbound Working:41:59
I am trying to write a formula that will show me if a user is making an outbound call and also showing on the Outbound working status, but unfortunatley these times will not match because one shows the time call was made and the other shows duration in status.
View 9 Replies
View Related
Jul 10, 2014
I created a macro that extracts two columns of numbers from one workbook and need to match them to another set of numbers in another workbook. For instance i have numbers like...
18314 907
18272 64
11005 907
11005 324
..Now i need to match these numbers up with the others to find the hyperlink associated with them.
View 5 Replies
View Related
Dec 30, 2008
I'm not sure if it's possible to do this, but I have three lists of data. One is a complete list (for example, the numbers 1-25).
The next list is a subset of the complete list (e.g., 1,3,5,7,9). Attached to these (the subset list) is another list (let's say letters, so A goes with 1, B goes with 3, etc). I want to physically move the paired entries from Lists 2 & 3 so that List 2 matches up with List
1. Let's see if I can represent this visually:
I have:
1|1|A
2|3|B
3|5|C
4|7|D
5|9|E
6|
7|
8|
...
25|
I want:
1|1|A
2|
3|3|B
4|
5|5|C
6|
7|7|D
8|
...
25|
View 3 Replies
View Related
Apr 16, 2009
I have 2 sheets in a workbook.Sheet1 is a master sheet with Customer code and there Sale.And Sheet2 is the sheet which has only Customer code.
Note: Its just a Example i have total 1924 data.
Now in Sheet 1 there are some Customer code which is Repeated some times 2 and 3 or even 4 times. In Sheet 2 Same as sheet 1 there are some data which is also Repeated. Now i how can i know which Customer Code in sheet 2 Missing from Sheet1
I have Colored Some Data in sheet 2 which is not match with Sheet1
For Example.
In Sheet 1 221000000232 is repeated 2 times but in Sheet2 has only 1 times.
View 2 Replies
View Related
Dec 20, 2013
I have created two sheets: "Roles to Courses" and "Datasheet". I have attached the zip db for review - its only 314k. I need to search the Datasheet in columns N, P for values from the Roles to Courses sheet (Business Role - SAP ECC) column A and find a match in the Datasheet. When a match is found, I need the B,C,D,E,F,G,H,I,J,K columns that contains the values of the classes from the Roles to Courses sht, to be added to the field called "Training Courses" in the Datasheet.
I started a macro but wasn't sure enough to complete the commands.
View 14 Replies
View Related
Mar 4, 2014
I am trying to match certain types of data from one datasheet to another. I want to match the address from the VOA worksheet to the local occupier. telephone number and local contact which are located on the raw data worksheet.
I have used the INDEXMATCH function but it doesn't correctly match the data properly as it fills certain fields that don't exist in the raw data.
View 2 Replies
View Related
Jun 30, 2007
I need to match data with specific criteria. Details of the problem are as follows:
I have 2 sheets: "Main" and "Sub"
In "Main" there are 3 Important Columns: Col "C", "I" and "AF"
In "Sub" there are 2 Important Columns: Col "I' and "N".
Value in column "C" of "Main" is same as value in column "I" of "Sub", but not in same order.
Now the specific criteria:
If value in Column "I" of "Main" is not equal to "Zero", then the macro should pick up the value from the corresponding column "C" of "Main" and search for the same in column "I" of "Sub". After matching the same, it should find corresponding value in column "N" of "Sub". Then the macro should pick up the value from column "N" of "Sub" and put the value in the column "AF" of "Main".
This is basic requirement.
I am unable to retrieve the right value for the same.
Fine tuining will be:
If the value in column "N" of "Sub" is "Fully Automated" then it should only write "Automated" in column "AF" of "Main". And If the value is "Manual + ...." then it should write "Manual" in column "AF" of "Main".
View 14 Replies
View Related
Aug 26, 2007
I'm trying to get data from one ws to another with corresponding keys, and wondering what the best and most efficient way would be to accomplish this.
INDEX...MATCH ?
Do While Not IsEmpty(ActiveCell)...ActiveCell.Offset(1, 0).Select...Loop?
Here's the setup:2 worksheets: source and target
source ws has 2 relevant col: A and K which are key/val pairs
target ws has x number of col
val from source ws are to be written in 1st avail blank col on target ws
some of the keys will exist in both ws, some only in one
if a key from source ws is not present in target ws, it will need to be added along with its val
The # of the first avail col is targetCol:
targetCol = Cells(1, Columns.Count).End(xlToLeft).Column + 1
View 9 Replies
View Related
Sep 5, 2008
What I am trying to do is pretty simple (this is what I imagine)
I have a 4 columns
- the columns 1 and 2 contain a number of days and an associated number
- the columns 3 and 4 contain a number of days and an associated number
My formula should do this:
- if the number of days (col 1 and 3) are the same, then multiply the number in col 3 and 4 together.
- more specifically, for each number in col 1, the formula looks all matching values in col 3 and makes a simple calculation
- result displayed in column 5
Constraints
- col 1 has more data than col 3
- the data in both column are not sorted
Dim x As Integer
For x = 1 To 10 Step 1
If Cells(x, 1) = Cells(x, 3).Value Then
Cells(x, 5).Value = Cells(x, 3) * Cells(x, 4)
End If
Next x
View 12 Replies
View Related
Jan 20, 2009
I need add a new worksheet that outputs sheet name and box 7 data for each sheet onto a master spreadsheet. I have been playing around some code but adding a third loop is a bit of nightmare and not ideal so I've just output to msgbox for testing per. Would application.match be better to use than the 'is like'?
View 3 Replies
View Related
Jun 1, 2009
How do I write an if statement where if the value from a cell (mtunit!A1) exists in a row from another worksheet (sqllist!A:A) then return the value of the of the next cell(sqllist!B2) from the row where it exists, else leave it blank.
View 7 Replies
View Related
Jul 21, 2009
what im basically trying to do here is to move XYZ from column B and 123 from column C to match up with XYZ in column A ....
View 7 Replies
View Related
Jul 29, 2009
I am trying to compare a list of product codes on sheet 1 column A, with a larger list of product codes on sheet 2 column B.
My aim: when i click to button on sheet one the part numbers that are in both lists (sheet 1 and 2) Should be moved into sheet 3 columns B and be rearranged so that they match horzontally with the full sheet two pricelist which will have now moved to column A sheet 3. This should leave gaps in column B where there was no matching part number.
View 12 Replies
View Related
