I need to line XYZ to XYZ as well as the information attached to XY&Z
i dont really know how else to explain it but i was hoping there was a macro or something out there that i could use to do this would make it a lot easier
I have a large database of research data where the first column contains different categories (labelled as I, II, III, IV, V, and VI). For each row there is a data value in the second column that is numeric. What I need to do is move the data so that the data are displayed in 6 columns with the data values listed each column heading according to the category label. In the example I've shown there are 6 different categories, but the number of categories (and hence the number of columns in the final resulting sheet) will change for different data sets that I'm using. Also note the the number of cases for each different category is different (so that each column will not be even in terms of the number of rows of data under each column heading). I hope I've been able to explain what I'm looking for clearly.
I've attached a sample file showing the sample data input that I have on one sheet, and then the desired re-tablulated outcome on the second. I have done this using the auto-filter function and copy/paste, but this will take far too long for the larger datasets I'm working with.
I have a macro that I am working on which I have gotten stuck on. It compares Column A and Column C and any numbers which match are pasted into Column D.
What I am getting stuck with is I need to copy and paste the data for column B to Column E as well. So in other words.
If column A and B match then the number from Column A that matches B will be pasted in column D along with the value that is right next to it in Column B.
Attached File : MatchingMacro.xlsm‎
I am trying to use a "=COUNT(IF(..." formula to count the number of hourly employees that have start dates older than 3 years. Column A has the start dates and column B has the salary status (S/H). =COUNT(IF(AND(A2:A1000<DATE(2004,1,1),B2:B1000="H"),A2:A1000)) This formula returns 1 no matter what I change the data to.
View 3 Replies View RelatedI am working on large sets of data (more than 50,000 rows of data). I have two sets of data. Set 1 and Set 2 (master data) on the same worksheet. Both the sets of data have three columns each. I am using EXCEL 2007. I was able to accomplish step 1 below.. but I am totally lost with step 2 since i have an additional criteria for the "year".
I have attached the excel sheet as well. This is what I am trying to accomplish:
1) I want to find exact matches in set 1 and set 2 and highlight it or do something to show that a match was found. The challenge is the data in set 1 can occur anywhere in set 2.
2) Add to the complication .. my criteria for matching the year is different. If the Set 1 "year" is equal to or greater by 1 yr or greater by 2 yr when compared to Set 2 "year", I want to treat it as a "match".
For example, from the data attached:
Set 1 data in row 4 is: ATLANTIC ABSECON 2004
Set 2 data in row 3 is: ATLANTIC ABSECON 2003
I want to treat these two data as "MATCH" since ATLANTIC matches ATLANTIC, ABSECON matches ABSECON and according to one of my criteria for year, Set 1 "year" is greater by 1 yr than the Set 2 "year".
I have two sets of data from columns A:N (O is blank) & P:AC. Column A & P are account numbers. I want to compare columns A & P for exact matches, there will never be any duplicates in either of these columns by themselves. If there is a match I would like that entire row to align, if there is no match I would like a row to be inserted. I have attached a copy of a worksheet of what I am looking to have done.
View 2 Replies View RelatedI have the following spreadsheet with 4 columns of data:
1000001245aMr Fred Blogs
1000001270a1 Blog Street
1000001270bNorwich
1000001270cNorfolk
1000001270eNRx x99
1000001270gThe Manager
1000001270k01603 555 555
1000001270l01603 555 555
1000001270qManager
1000001270xN
1000001270yN
1000002245aMrs Fredilina blogs
1000002270a10 Blog Street
1000002270bNorwich
etc etc for about 36,000 rows.
What I need is for each unigue reference ID (column A),
I need all corresponding data moved up into a single row in serperate columns, something like this:
1000001 |245|a|Mr Fred Blogs| 270|a| 1 Blog Street|270|b| Norwich | 270|b|Norfolk |etc
1000002 |245 |a |Mrs Fredina Blogs |270|a|10 Blog Street |270|b|Norwich| etc
Each entry doesn't nessesarily have the same number of rows.
I would class myself as a beginner VBA bod, but the solution to this one is illuding me a present.
i have data that has several bits of information for each different account number.
is there a way of writing a macro to transfer the information from the main sheet onto different sheets automatically titled for each of the different account numbers?
ie. Account Cost
E1 £100
E1 £200
E2
E2
etc
i want different sheets for the info next to each of the account numbers
I have address data all in column A (each paret of the address is on a separate row) which I need to move to separate columns on one row for each address:
ie:
Mr Bloggs (row 1)
123 The Avenue (row2)
The town (row 3)
The postcode (row 4)
Mrs Smith (row 6)
456 The street (row7)
The postcode (row 8)
needs to be: (I have used || to indicate different columns)
Mr Bloggs || 123 The avenue || The town || The postcode
Mrs Smith || 456..... || .......
The problem you see from the above is that they are not always consistent ie. the postcode (zip) isn't always 3 rows below the name for example. Also although generally there is a blank row between each record (this may sometimes be 2) and equally there may also be times when there is a blank line in the address.
Any ideas on how to do this.... I have 1000's of records and really can't afford to go through each one making it consistent before moving it to columns?
I have a macro that would check data in Column A and validate if a particular number is repeating, then for that number go to column B, Take the Values from there go to a new sheet and paste the values in a row.
CurrencyDateRef CodeIDAccountAmountDes.USD07152011XDVU4315210.4200.C5001.USD-18,606,772.190Distr Payable 07152011USD07152011XDVU4315210.4200.C5002.USD-111,131.450Distr Payable 07152011USD07152011XDVU4315420.4240.C5001.USD18,606,772.190Distr Payable 07152011USD07152011XDVU4315420.4240.C5002.USD111,131.450Distr Payable 07152011
I get the data in the below format
CurrencyDateRef CodeIDAccountAccountAmountDes.USD07152011XDVU4315210.4200.C5001.USD420.4240.C5001.USD-18,606,772.190Distr Payable 07152011USD07152011XDVU4315210.4200.C5002.USD420.4240.C5002.USD-111,131.450Distr Payable 07152011
I need to the macro to get the data not from the second cell.
Below is my macro
Sub test()
Dim idRange As Range, c As Range
Dim uniqueID As String
Dim destSht As Worksheet, sourceSheet As Worksheet
Dim r As Long
Dim i As Integer
Dim map As Object, key, item
[code]....
Current Data:
File 1:
Each set of data is listed in either two or three rows
Eg.,
ID
Date
Filename
ID
Date
ID
Date
Filename
The goal is to move them to separate columns (rows can be 3 or 2 for each data set, and may or may not be separated by space/additional row)
File 2:
Has a common field 'ID' as that of File1, does not have Date, and Filename, but has a new field 'Detail' (already in the expected format)
Eg.
ID Detail
The goal is to merge properly formatted data from File 1 to File 2
Eg
ID Date Filename Detail
I have an excel spreadsheet where the columns have the 12 months of the year in Row 1. What I need is code that will find the month (from the column) which I select (via a listbox in a userform) and then go down that column and move the data that corresponds to that month to the userform.
So, If I select January from my userform, all data below January will then be moved to the userform and the same thing will occur with each month I select. I know how to move the data from the sheet to the userform, I just don't know how to match my data with the selected month.
in moving data from multiple columns to a single column. I have attached a sample image from an excel file which details the requirement. The first column contains a qualifier, that should remain constant when the data from columns B to the end move to a single column "B". The number of columns for each row is different, however, there is a maximum number, say 25. As mentioned in the image, when the data from columns B to the end is moved in to column B, column A is retained fixed, to the original value, and the original data below it is pushed below. Any pointers to how this can be achieved by VBA or without VBA?
View 6 Replies View RelatedI'm trying to work out how to take all cells with data from multiple columns and stack them in a single column.
Here's the history...
I have multiple part numbers in single cells in column A. I perform a text-to-columns function. The resulting part numbers spread across multiple columns (say, B through K). Now I need to get all the part numbers, in their own cells, stacked in column A for one continuous list of single cell part numbers.
Is there a VBA option for cutting only the data cells from Column B-K and pasting the data at the bottom of column A while avioding blank cells?
import it into to Stata to do statistical analysis. I always receive spreadsheets like this:
country
1991
1992
1993
1994
1995
1996
1997
1998
1999
2000
[code].....
and I have to manually reorganize it like this to import into Stata:
country
year
value
Benin
1991
20
Benin
1992
254
[code].....
Is there way I can quickly design a macro to do this? The problem is that I generally have a list of about 60 countries, and years from 1991-2011. So, it's really time consuming copying the column of data corresponding to the year, pasting below, repasting the list of countries and the years...then again..then again...then again...I'm using Excel 2010.
I have a worksheet which contains 2 columns which is needed to work my problem.
Unique Work ID and Description
The unique work ID are the same for each description, but there are up to 5 different description associated with each unique work id.
I'm looking for an automated process but where to start to convert the 5 rows in the unique row and 5 column for the descriptions
removing duplicate rows and move other data frm rows to columns.xlsx.
I am attaching a sample excel sheet showing what I need to do.In the first tab, I have a list that includes duplicate rows (first column only). I want to remove those duplicate rows but I don't want to lose the data in the following columns which can be unique or duplicates as well.
see the desired result tab in the sheet to get an idea of what I am looking for as the end result.
Keep in mind that the actual source file I am working with could have up to 50000 row, and the expected results could be around 2000 rows. So nothing can be done manually.
I was wondering if there is a way to write a macro that will take an excel file that I have called Alldata and then have it copy the first two sheets to a new workbook named something else( FullSparameter_0) and then copy the name of the third and fourth sheet into the new FullSparameter_0 workbook and then copy column A-E from sheet dB Alldata.xls and paste it into the FullSparameter_0.xls workbook in the same dB sheet in the same spot. And do the same thing with the Phase Sheet. i know this sounds confusing, but im really new to the VBA stuff.
I just need 15 different files from the original Alldata.xls that will be named FullSparameter_0, FullSparameter_1, .., FullSparameter_14. but each file will have the same two first sheets, Setup Information and Cal Verification, and the same last two sheet names, dB and Phase, but then in the dB and phase sheets, it will copy over the next four columns of data. so in FullSparameter_0 it will have column A-E from sheet dB in Alldata.xls in the dB sheet in FullSparameter_0 (Same with the Phase Sheets). Then in FullSparameter_1 it will have column A and Columns F,G,H and I (for dB and Phase). In fullSparameter_2 it will have column A and then J,K,L and M. Each FullSparameter_X will have the exact same first two sheets and then the other columns for the last two sheets will be pasted into columns A-E in dB and Phase.
If a row starts with T3:X3 matches up with the rows in range T6:X8605 then goto that matching row... perhaps could be done with a macro?
View 9 Replies View RelatedI have two lists of email addresses in columns and I need to find the matches between columns and have those addresses returned to perhaps a third column.
View 2 Replies View RelatedI have many small spreadsheets of data organised into 4 columns and anything upto 250 rows. I want to be able to test if all the cells along each row have the same data in each of the 4 cells, so 4 occurences of the same thing. Each row of data will be different. (I'm not intertested in matching the data going down the column).
However, I won't know with each spreadsheet what is the exact data I will be looking for - if all 4 cells don't have the same data, that is fine as this will then require my manual attention which is the purpose of my project.
I just want to avoid having to check through lots of rows of data where all the data does already match.
The data will usually be text but it can be IP addresses and numbers too. I need a formula or set of operators to use for is exercise.
i have one work sheet. Column A contains 800 names bolumn B contains 15,000 names.
I need to compare column A to column B then if the name exists displat the names in a seperate column.
Using VBA... I have Column N (eFIUnitRebate) which I want to swap with Column M (FFUnitPcnt)
Now with Column M as eFIUnitRebate and N as FFUnitPcnt I want to create an extra column (adjUnitCost) where Column N is and push FFUnitPcnt to become column O, etc.
Here is how the whole process should work:
Swap M and N
Insert new Column in N
Insert new Column in P
Insert new Column in Q
Insert new column in R
There is more information on the sheet after these columns so for all column inserts it needs to push whatever was there over.
So the layout is as follows:
J
K
L
M
N
O
P
Q
R
Units
UnitCost
UnitRebate
[Code] .....
These columns are thousands long, so I would like the formulas for them to be applied to the whole column as well.
I am trying to match words in two separate columns that are not exact matches. any formulas or codes I can write to do this.Below is an example.
Column A
3m Corporation
Apple Inc
Allstate Corp
State Farm
I have two columns of card numbers (16 digits) in text format, as column A and column B. In column C, I'm trying to figure out how to compare column A and B for matches, and display the match in column C, if there is a hit.
View 4 Replies View RelatedI've been searching for a method to compare two large columns of numbers and remove replicates from the "main" column. Column A has all the numbers that are in column B and many more. I want to remove all the numbers from column A that are in column B. The result being two columns with no matching numbers. (or a third column that has only the numbers from 'A' that are not in 'B').
My search results on this forum have found results that compare rows for matches & can make deletions; and also one method that could mark all the entries that were duplicates in both columns, but nothing to remove them. The datasets I am using have over 400k entries in some columns, so manual removal of marked ones is not feasible. I need an automatic deletion method.
I want to compare two columns (from two different files, but copied into a new sheet) for duplicates or identical entries. But here's the trick, each entry contains atleast a number of digits. I'm not quite sure how to find items that may be 80% identical (in the right order, but might be messing a digit, or there is a dash or a 'o' instead of a '0'). I know how to do it in C++ or Java, but not quite sure how to do it in VBA/macro. Here's an example of what I meant. Let's say column one has the following:
0244-34
9393-O0
3932-44
and second column has
939300
493384
938923
If I was comparing second column to first column, it can pick up the 9393-O0 one because it's close enough to 939300.
I know there is a forumla to compare a small database to a list, but can't find it (I used to use French Excel).
Here is an example :
In a sheet, my small database :
(in two different cells)
Value1 and ValueA
Value2 and ValueB
Value3 and ValueC
etc...
In another sheet, my list :
I have a list where there's a lot of Value1, Value2... in one column, and in the next column, a lot of ValueA, ValueB....
I want to check in my list : if I have Value1, then, on the same row (next column) I have ValueA, if I have Value2, then, I should have in the same row (next column) ValueB....
And if I have Value1 and in the same row (next colum) ValueB, I want some kind of "Error" text.
Can't find this function nor (of course) the criteria to use it correctly.
I have two columns, one with the target text (D:D) and one with text I want to compare it too (A:A). If the text in column (D:D) matches the one in (A:A), I want to return the value in (B:B). I attached a sample of the problem i'm dealing with.
View 5 Replies View RelatedColumn A = numerical IDs, multiple entries, may have duplicates that should only be counted once in comparison
Column B = numerical IDs, multiple entries, may have duplicates that should only be counted once in comparison
Need: 1 cell that compares all of the A range to all of the B range and returns a a percentage of repeats. So, if there are 50 entries in A, and only 40 are unique, and B includes 10 repeats (unique, not multiple), then the returned value would be 25%.