Reorganizing Large Sets Of Data Composed Of Same Structure?
Jan 3, 2012
I have a spreadsheet that consists of 981 rows and 1152 columns (192 * 6). It is essentially 981 rows by 192 columns rectangles (if we think of it as a range) side by side. What I want to be able to do is cut 981 rows by 192 columns at a time and paste at the bottom of the first rectangle so forth so on.
Essentially currently data sets look like 1 2 3 4 5 6 - I want the cut paste process to move these rectangles to:
1
2
3
4
5
6
View 1 Replies
ADVERTISEMENT
Mar 27, 2006
I have a problem with VLOOKUP looking in sorted lists of more than 16384 items.
Basically it fails at 16385th item
I have developed a VLOOKUP function that looks up in multiple tabs in
multiple files and returns a value. However I have discovered that it fails
at row 16385. As Excel copes to 64,000 rows I regard this as a bug. Has
anyone else encounteed this problem? is it just my machine, is there a
solution / bug fix?
If I use FALSE when the item to be found is beyond 16384 the function
returns the 16384th value this is a warning to other users who may not have
noticed as I did not initially.
--
View 12 Replies
View Related
May 10, 2007
I have data organized in the following format:
Name aaaaa
Adress bbbbbbbb
Phone ssssssss
Situation aaaa
Name dd
Adress ggggg
Phone bbbbs
Situation eerr
Name hhhh
Adress ddd
Phone llllll
Situation ttttt
and it follow this same patern
Is there any way to have this data reorganized so that all the names go in the NAME COL, all the addresses go in ADDRESS COL and so on.
View 6 Replies
View Related
Jun 3, 2006
I have two data sets one measured and one modelled and I am testing a conceptual model that proposes that only where the tails of the probability distributions of each data set crosses will you experience an event. I'm enclosing an example file which contains both data sets and some descriptive statistics as well as the associated histograms and a diagram of what I'm ultimately trying to achieve. My question is how I go from the data sets I have with such a large discrepancy between them to plotting their probability distributions?
View 5 Replies
View Related
Apr 5, 2007
I have a spreadsheet that has data all in ONE column, column A. This data is shown in the attachment. Is there an easy way to seperate this data into its own columns? ie First Name, Last Name, Title, Company, etc?
View 5 Replies
View Related
Jul 22, 2013
I have 246 rows of data. Each row has 4 values, reading from left to right.
I need to make this all into one simple column, making 984 rows (246 x 4).
View 4 Replies
View Related
Oct 13, 2012
Reorganizing data, that would probably be best achieved with a macro. I've attached a file that represents my situation fairly well. There are 2 tabs, an Input and Output tab.
The input tab has Names along the left side, that may change in number frequently. Then along the top I have metrics, and values corresponding to the names. What I need a macro to do is to basically create an "entry" in the Output tab for each value.
The output tab is in a specific order, but basically there are 8 columns. The 4 unspecified columns don't need to be filled. The other 4 represent data from the input tab. The "Metric Value" can simply be filled with 0s for now.
Excel - VBA Assistance.xlsx
View 5 Replies
View Related
Feb 5, 2014
I've got a problem with organizing my data. I've performed a study with several participants, each of which does several trials with 8 conditions (1,2,3,4,5,6,7, or 8), with each participant doing a condition more than once. I also have a separate column telling me whether they responded correctly or incorrectly (with a 1 or a 0).
I need to find a way to produce a new column to identify whether they got each condition (of the conditions 1,2,3,4,5,6,7, or 8) correct separately, i.e one column for responses to condition 1, one for condition 2 and so on....
It would also be useful if there was a way that once this is done I could summarize their accuracy of responses to each condition.
I've attached an example of my data. excel problem example.xlsx‎
View 14 Replies
View Related
Feb 15, 2009
i understand how to make a formula that shows if a number is prime or not:
View 4 Replies
View Related
Dec 1, 2013
The attached file shows what I'm trying to do - I'd like to fill in the data in the top left corner table (blue-shaded) with the data combined from the two other tables. Think of the Greek letters as names that are grouped into the groups named {A,B,C,D,E} (the names have no significance, it's just an example).
I was able to do so using an array formula and an extra column (yellow shaded) as you can see in B2 - however, this solution doesn't suite my case because the actual tables will be coming from automated processes and cannot be easily manually altered.
I tried inserting the array formula into the sumproduct formula - see B3 - but for some reason this doesn't behave the same as when the array is outside the formula. I should also mention that I came across a user defined function solution - this too is not so suitable for me right now.
View 9 Replies
View Related
Jul 28, 2009
I have an organizational hierarchy dump in excel from an SAP ERP system. The format of that file is less than user friendly and I need to convert it to a flat file format so I can do pivots against it or possibly dump it into access database. The background is as follows (I am also attaching a file).
The organizational structure is multinoded/layered. Top most node is a company second from the top are divisions designated by 02DivName each 02DivName has multiple nodes assigned to it. The lowest level of the hierarchy is a department, always a numeric, always same length.
That's where it gets interesting. Each node/layer under 02DivName has a technical description and text description they get broken up into two different columns when SAP dumps them. Technical descriptions can be used to identify where in hierarchy the node falls(parent/child). For example 02A is a child of 02DivName 02B is a child of 02A 02C is a child of 02B and of course all of them roll up to 02DivName. There could be multiple 02A's under Div Name and their respective 02B's, C's D's would roll under them. So on export file it would be graphically represented 02A1 blank blank blank blank in that column and then change to 02A2 their respective 02B's would be in different columns works the same for C's and D's.
Here is another wrinkle the department is not always assigned to the lowest level grouping. I could have 02DivName dept, dept, dept, and then 02A group assigned to 02DivName. The department numbers end up on export file in the same column as fre form text descriptions for the nodes. It is staggered format.
The object of what I'm trying to do is basically convert the gibberish described above into flat file format via a vba script. I need to have node1 node1 desc node2, node2 desc, etc etc in columns the last column being a department number. So if for example I have a dept assigned directly to 02DivName directly all the other node descriptions will be blank if it's node 02A then the node1 and node2 will be populated. Any help will be appreciated. And here is the file with excel spreadsheet
View 5 Replies
View Related
Mar 23, 2009
I have 570 records in one sheet and 330 on the other one. They are the same data and I want to match them but i have been doing so manually because in the one w/ 330 there are missing values, so I have been copying and pasting but takes too long. The 570 is complete, i took it from the census data and the 330 is not.
I want to be able to match them in the same row including missing values so I can use it in ArcGIS
I have tried to match w/ the match function but have not had luck...
View 8 Replies
View Related
Jun 20, 2008
I try to understand what the syntax is to create and use an array named constant which would be composed of other constant.
Example :
Constant called AvgZone1 which is =AVERAGE(zone1)
Constant called AvgZone2 which is =AVERAGE(zone2)
and then create a named array constant based on the names : =AvgZone1,AvgZone2
is not working. You cannot for example do a SUM().
View 9 Replies
View Related
Apr 10, 2008
Below is a series of sets. Column A is the set number. I need a macro that will insert a row between sets and then put a border around each set. In my spreadsheet the sets are from A1:C500. Sometimes the sets are only 1 row, sometimes multiple rows. It looks like I will be doing one of these sheets every week. So far I have been doing it manually, but a macro sure would save some time.
View 13 Replies
View Related
May 12, 2012
I have two separate worksheets:
I'm trying to find a formula that looks at Column A on both sheets (each client is allocated a unique number) and if they match enter in column D of the referral sheet the month they were seen but only if it is a 1st contact (appt type on column D of contact sheet)
Referral
A
B
C
[Code]....
way to do the calculation using Excel 2003
View 9 Replies
View Related
Jul 13, 2013
I 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".
View 1 Replies
View Related
Oct 5, 2009
I have tried nested ifs and vlookup to compare two sets of data and change the first set of data if it is than the first. But leave it alone if it either is the same or does not exist in the new set of data. It sometimes seems to work but i find it is not consistant. It looks simple but i think i am missing something.
if column A has identifiers and column B has results then it should work if the identifiers in column C are found in column A and it looks to see if column B and D are the same, then change B if different but leave it if either it is the same or not there.
A
code1
B
36
C
code1
D 33
View 9 Replies
View Related
Jul 8, 2014
I have two sets of data for students. One set contains all the students with certain test scores, taking up columns A to N. The other set contains about 80% of those same students with a different set of test scores. Students are sorted by ID numbers. How do I combine the second set into columns O to V so that student ID numbers match and it inputs the rest of the student data into the sheet.
I know there will be some blanks because not all students will be there but I need them to match up, even with the blanks.
View 2 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
Nov 8, 2013
I'm attempting to create a mailing list for the company I work for, but the software that has the data record is very limiting. The two tables I received from the program are listed:
Customer
Number of Sales
name 1
#
name 2
#
name 3
I tried to illustrate that the address list has a different customer composition than the sales list; the address list has customers that the sales list doesn't have. This means I can't just sort alphabetically and drag the addresses over. My goal here is to create a datasheet that incorporates both the address and number of sales so that we can send the top 500 customers a mailing. How can I do this?
View 2 Replies
View Related
Apr 1, 2014
I have 3 seperate tabs of data that i want to put into a combined sheet, how would i do this, i assume it's just like a copy/paste exercise but not sure how to do this using VBA?
My tab names are;
FND Data
UL Data
Life data
I just need the entire range copying from each tab into the 'Combined' tab
View 5 Replies
View Related
Dec 13, 2009
In the attached workbook, I have identical sets of data in column A separated by an blank cells. I need a macro for user form that searches for an item indicated in the textbox of the form and then delete the whole row in all sets of data.
It is important to keep the blank cells between the sets constant, 4 blank cells between each set, except the first set that starts with 3 blank cells.
View 2 Replies
View Related
Jan 21, 2010
I’m trying to get the total number of participants in two different sets, with selection criteria attached to both (I’ve attached an excel file, as the example with those scoring +ve highlighted in yellow). The first set A (b1:b15) I want to select all values above 5, and the second B (c1:c15) set above 10. There other provisor, is that the A and B are further split into two groups (one above and one below 20).
In summary, I need to count all the A>5, B>10 (And both have to be above 20 in column D), but not to double count the ones that have Both A>5 and B>10. I don’t want any duplicates to be counted, so summing the total of A and adding it to B won’t work. In essence it’s an attempt to use the A Union B function that you’d observe when using venn diagrams.
Is there a simple way/formula of doing this?
View 13 Replies
View Related
Dec 6, 2006
I manually reconcile two sets of data every month by sorting by PO# and then manually matching up the onse that are the same and then calculate if there is a variance in $ amount.
The 1st set of data is a list of invoice PO#'s and invoice $ amounts and the 2nd set of data is order PO#'s and order $ amounts. What I need to do is reconcile the orders to the invoices by PO#. It is very common to have a PO# on each list but have a different $ amount and I only need to know what the difference is between the order amount and the invoiced amount. I also need to know what is on the invoice list and not on the order list and vise versa....
View 10 Replies
View Related
Feb 23, 2009
I am wondering if it is better to do this in excel or access. I am pretty familiar with access...i know this would be easy to do if querying from one table and doing a relationship between identifier, but i'm not sure how to capture data from both tables. But basically this is what I want. I have 2 files, which have identifiers and share amounts...both files will have some like identifiers, some not alike...basically this is what I want to do: ....
View 9 Replies
View Related
Aug 28, 2009
I'm trying to compare two sets of data on the same sheet. Each set has 4 columns...A - D and F - I. I would like to take the data from D and I (range 20 to 50) and compare them to see if any differences exist in the two columns. If there is something different I would like to bold and highlight the cells (A-D) in that row, and the same goes for the second set of data (F-I).
I'm trying to do this with macros so I can add it into my existing code.
View 9 Replies
View Related
Nov 10, 2013
I have 2 sets of data/array/range. Each set consists of 2 columns with a large number of rows.
I want to compare my 2nd data to a master data. And list if anything is different in 2nd set of data from master set in column A than highlight the difference or copy the value to another place.
Also want to compare the 2nd column if column A was same and consider both column A and column B for that associated row different if column B is different.
The trouble for me comes in because the list is never alphabetical (sort doesnt work cause of funky naming) and never of the same size.
Attached is a photo of an example for maybe an clearer understanding. Also attached an example excel sheet I tried it within excel but cant seem to figure out how to look also for the 2nd column, so im trying to avoid the within excel route and go using vba ...
T3OCcxw.jpg
example.xlsx
I attempted it with a very basic code thats not working =/ just cant seem to figure how to code to get the desired result
VB:
Sub matchdiff()
Dim cell As Range
Dim found As Range
[Code].....
View 1 Replies
View Related
Jul 8, 2009
Sheet1 contains a large set of data, including a date and a corresponding value.
Sheet2 (Summary) has a column called "Begin Date" and a column called "End Date". How can I use a formula to sum every piece of data that fits within the two dates?
View 5 Replies
View Related
Oct 28, 2009
I am trying to compare two sets of data.
I have a set of All zip codes in 10 counties in one worksheet:
ex:
Column 1 / Column 2
30303 / Fulton
30307 / Fulton
30310 / DeKalb
30234 / Cobb
But this sheet has about 300 zip codes
Then in a second worksheet I have dollars by zip
30303 / $10,000
30456 / $56,000
I'd like to make a third column in this sheet that fills in what county. Does anyone know how to search sheet one for the appropriate zip, then stick the word beside that zip into the column 3 secion of the dollars by zip code?
I'd rather not have to search every zip code in the dollars section one at a time. Not all the zip codes in the state are represented in the second sheet, so it's not quite as simple as just lining them up.
View 13 Replies
View Related
Apr 3, 2013
Create one X-Y SCATTER graph (with Smooth Lines and Markers) with the Average Movement Time (MT) on the Y axis and the Index of Difficulty (ID) on the X axis for each group member during STATION 1: PAPER BASED VERSION OF FITTS' RECIPROCAL TAPPING TASK and STATION 4: PAPER BASED VERSION OF FITTS' RECIPROCAL TAPPING TASK: WEIGHTED
ID stands for index of difficulty, the graph is supposed to indicate that as the index of difficulty increases (3) movement time increases and as it decreases (2) movement time decreases.
Lab 8 Data Q 4 attempt.xlsx‎
View 5 Replies
View Related
