Create From The Data Set In Column Set 1 The Data Set Shown In Colunm Set #2
My data is in colunm set #1. I want to create from the data set in column set 1 the data set shown in colunm set #2. I want to automate the process. The program needs to regognize if a name appears only once or more than once. If it appears more than once the data needs to be merged as I highlighted from yellow to green.
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Set Print From First Column To Last Data Row
i need a macro that will set print from A1 to H (last data row) if age entries in col h is >=5, i dont want to print data below this age criteria Over2day CDEFGH6Value DateEntry DateTypeAmountCCYAge71-Oct-075-Dec-08LDR2,203.67USD729819-Nov-075-Dec-08LCR200.00USD680924-Jun-0817-Dec-08SCR2,001.38USD462 Excel tables to the web >> Excel Jeanie HTML 4
View Replies!
View Related
Manipulating Matrices (set Zones And Convert It To Another Set Of Zones)
I have an origin and destination matrix with one set of zones and need to convert it to another set of zones. This involves combining some zones together. I tried a sum if, but it didnt work. I have done the calcs individually but will have to repeat the process so was looking for a wuicker, more efficient and less error risk! Just to clarify, I have to convert a 23 by 23 matrix into a 14 by 14.
View Replies!
View Related
Summarising One Set Of Data In Another
I'm trying to a few things with the following data. Top table is, well, the table at the top" . The Analysis table is currently blank below the headings: Step through each diagnosis in the top table and add it to the lower table if it's not already there (no duplicate values). Then work out the average waiting time for each one and how many times it's there. I'm having particular problems with defining "ADiag". Dim Diag As String 'Diagnosis values in top table (e.g. E2=Varicose veins) Dim DLastRow As Long 'Last row of data in top table (currently 19) Dim ADiag As Range 'Diagnosis heading in Analysis table (currently a23) Dim DiagList As Range 'Diagnosises in Analysis table (currently 0).......
View Replies!
View Related
Set Up Data For VLOOKUP
I have attached a sample. I want to return a 'Team Number' to column C. The data is from another sheet, however in the sample I put it in column H and J. Should be simple VLOOKUP but columns won't match exactly.
View Replies!
View Related
Condensing A Data Set
I want to ask if there is a way to simply extract non-empty rows from a data set consisting of many rows filled with zeroes and some which are filled with the non-zero values. By “simply” I mean without using any macros. Any help or suggestions are highly appreciated. Dima I have attached the workbook with example of the sheet with such data set (CLEAN tab shows the desired result of this operation): [url]
View Replies!
View Related
Reversing A Data Set
imagine your data set (numbers are random and do not start in cell A1): 1 2 3 4 5 6 i need the entire data set reversed 6 5 4 3 2 1 i started trying to code this...... but quickly realized i havn't the slightest idea where to start
View Replies!
View Related
Vlookup To Compare Two Sets Of Data And Change The First Set Of Data If It Is Than The First
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 Replies!
View Related
Automatic Search Of Data Set
I'm kind of rusty with spreadsheets and Excel 2007 is entirely new to me. I'm not even sure what I'm trying to do would be called. I have a spreadsheet that is a list of records; a name, ID number, one text, and four numeric columns per record. I would like to make a set of buttons or something that will automatically do a custom sort. Basically a "sort by this criteria, sort by different criteria" etc. so I don't have to manually do the sort repeatedly.
View Replies!
View Related
VBA To Clean Data Set
Sheet1 A1Bad Data2Bad Data3Bad Data4Bad Data5Bad Data6Bad Data7Bad Data8Bad Data9Bad Data10Bad Data11Title12Good Data13Good Data14Good Data15Good Data16Good Data17Good Data18Good Data19Good Data20Good Data21Good Data22 23Bad Data24Bad Data25Bad Data26Bad Data27Bad Data28Bad Data29Bad Data30Bad Data31Bad Data32Bad Data33Bad Data34Bad Data35Bad Data Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4 How can I ensure that all Rows before "Title" and all Rows after the last populated Row (denoted by the fact that there is a single blank Cell after it) are completely deleted?
View Replies!
View Related
Lookup Data Between Set Values
In D16:D29 I use vlookup to return values from named range 'mforcetable' based on value in cell C16. However, if values are entered in cells I36 & K36, I37 & K37 and I38 & K38 I need the lookup to return the values in 'mforcetable' including and between the 2 values. See attached example. Based on value 2000 in C16, what I need to see in D16:D29 is 10.0 20.0 40.0 100.0 100.0 200.0 400.0 600.0 800.0 1,200.0 1,600.0 1,600.0 2,000.0 0.0 Where the 100.0 and 1,600.0 are repeated because both values are specified in I36 & K36, I37 & K37 or I38 & K38.
View Replies!
View Related
Extracting Desired Data From Raw Data Set
I need to extract some part data from available raw data. The data format is like this ABC-00000000. The problem i am facing that this data is placed in rows that contain other information like this. e.g. "Bill # ABC-00002345 realized" some times the data is fed in this fashion "Bill # ABC 2342 realized" or "Bill # ABC - 3232 realized" or "Bill # ABC000032324 realized" now i need to extract the data highlighted in red and arrange it like data highlighted in green. I tried to use mid and find function but could not arrange the extracted data in to desired form. A VBA code or formula is required.
View Replies!
View Related
Getting Excel To Calculate All Combinations Of A Set Of Data
I've found myself having to use a spreadsheet to calculate some acoustics problems, (problematic frequencies, called modes, in orders ranging from 0,1,2,3....). Ideally what I want to be able to do is simply to get Excell to calculate all the possible combinations of these modes. A combination could be: 000 011 101 110 111 012 143 etc. Obviously this is a nightmare to input by hand, is there any kind of function I can use to help with this process? Also these frequencies only need to be calculated up to a certain point, so is it possible I could also instruct the cell to only display a value if it is less than a value in another cell?
View Replies!
View Related
Macro For Multiple Tabs From A Data Set
I'm not very good with macros and I need to create a macro that copies data from one excel worksheet into multiple other worksheet tabs in the same workbook. I have 8 columns and thousands of rows of data. The spreadsheet is sorted by column E. In column E, there are about 25 different values going down throughout the spreadsheet. I would like the data for each of these Column E categories to be copied over to a new tab in the spreadsheet with the tab name as the value in E. So in the end there would be the main tab, and then 25 new tabs with the filtered data. Does anyone already have a macro that will do this?
View Replies!
View Related
Two Different Conditional Formulas Using The Same Data Set And Cells
i am trying to write a formula that calculates the difference between two cells, i.e. the buy price and sell price of a stock, to result in the profit/loss of the trade. However, the problem is, When "going long" i need to calculate b-a = c C:C=B:B-A:A but when i go "short" i need the calculation to be a-b = c C:C=A:A-B:B i am thinking that if i have a column, say d, indicating either "long" or "short", as a conditional argument, then this can determine which formula to apply to the calculation... e.g. please try to make sense of this... if "d=long" then C:C=B:B-A:A, but if "d=short" then C:C=A:A-B:B is this sort of calculation possible? any suggestions would be greatly appreciated. i have just finished reading a formula text book, but if the argument im creating isn't exactly shown word for word i am having trouble adapting the formula as needed.
View Replies!
View Related
Calculate Cycle Length Of Data Set
I have a data set that contains a repeating cycle. I want excel to find the first and second occurrence of the data crossing the zero axis and then report the time between the zero crossings. The data is not purely in ascending or descending order and often just changes from positive to negative or negative to positive without hitting the actual 0 point.
View Replies!
View Related
Large Data Set Summing Macro
See attachment. For example, imagine data in cells A2:N25000 (obviously, this example in the attachment has been shortened). At this point, also imagine that rows 7 & 8 did not exist. So, there is a continuous stream of data for vaious counties in A2:N25000. For the purpose of context, the data contain mortgage information for all the lenders in a particular county for an entire US state. Is it possible to create a macro that would insert two rows after each county in the data range (e.g., rows 7 & 8 in the attachment)? In other words, in the first row a macro would insert a row (row 7) that sums the results of the top 25 lenders in each county. Then, the macro would insert a second row (row 8) that sums the results of ALL the lenders in each county?
View Replies!
View Related
Listbox Selection - Data Set If Statement
I have a spreadsheet using drop-down boxes with data from a data sheet. What i need to do is, if 'A' is selected in "A3", then in "B3" i want a drop-down box to select "A only" compatible values, whereas if 'B' is selected in "A3" then i want "B only" compatible values to be used in "B3" list box/drop-down box. E.g. if "A3" is 'select transport' then if the value in A3 chosen is "Car" then i only want "4 [x] wheels" in drop-down "B3" cell.
View Replies!
View Related
Set Cells With A List Data Validation
I have a list and set cells with a List Data Validation so you can choose them from a drop down menu. What i want to do is when you choose one of the option is the cell next to it come up with other option relating to the previous. E.G. I choose John Smith in cell A1 as a manager and in A2 a list of his team appears.
View Replies!
View Related
Create A Set Of Named Ranges
I'm trying to make my worksheet generate a bunch of named ranges whenever it is activated. However, I'm getting a "Not a valid name" error on the line in red. The value of r is a string, so I really don't see what the issue is. Another time, I got a object defined error, but I think I've declared everyhing too. That line is very similar to the line above, and tends to work once (the first time around the loop). After that, I get the errors I mentioned.
View Replies!
View Related
Creating A Graph By Auto Selecting A Set Of Data
i have a set of data range from B1 to K10 (10 cells all together). and i created a graph by selecting from B1 - K10. what i want is, if i key in range from 1-10 in A1, the graph will change based on the number i keyed in (from 1-10). if i keyed in 5 in A1, the graph will auto select B1 to F1 and show data from B1 toF1 then if i select 6 in A1, the graph will auto select B1 to G1 and show data from B1 to G1
View Replies!
View Related
Identify Multiple Maximum Peaks In Data Set
I have a set of data about with approx 7500 cells all contained in one column. The data has a series of peaks that happen, and I need to identify each peak value and place it in a cell. The peak does not occur at regular intervals but they are somewhat regular, as in within 130-230 data points. So, if one could find the first peak in the first 200 cells, identify it and store it somewhere, then look in the next 200, store it, etc. I don't know how to do that in excel.
View Replies!
View Related
Extracting Data: Extract Information From A Given Set Of Cells
I am trying to write a formula which will extract information from a given set of cells. The information format is XXXX-YYYYY-ZZZZ What I need to do is pull out the information which precedes the second. so in the above the answer would be XXXX-YYYY. I was thinking of using a mid or left, right formula till I discovered that the # of characters for X, Y and Z can be any length ie they could be X-YYYY-ZZZZ XXXX-Y- XX-YYYY-ZZZZZ So the formula needs to find the second dash (-) and extract information prior to it.
View Replies!
View Related
Code To Filter, Sum And Return A Value From Data Set
I deal with large amounts of data. For a score card I need find a way to filter data based on two constraints, sum it and display the output in a table. The two constraints are: Business Unit and Maintenance Type. I played around with recording filters, but the code becomes very unwieldy. From past experience with this site, where I used 10 lines, there's a way to do it in 2. take a look at the attached example book, a truncated data set and the output.
View Replies!
View Related
LARGE, VLOOKUP- Set Of Data Covering Cells A8:C71
I have a set of data covering cells A8:C71 Column A lists locations - there maybe upto 6 entries with the same location. Column C lists a unique serial number. The list is sorted by column A (A-Z). For this example say location LONDON has 3 entries, with serial numbers 120, 125, 128. In cell F1, i want to display the lowest serial number for LONDON In cell F3, i want to display the 2nd lowest serial number for LONDON In cell F5, i want to display the 3rd lowest serial number for LONDON I have tried the following formula, with no success.. =LARGE(VLOOKUP(I9,A8:C71,3),1)
View Replies!
View Related
Using Dates Ranges To Alter Pivot Table Data Set
I would like to use a pivot table to manipulate my data. However, I need to be able to select data within my data set by a range of dates (usually a week). Is there a way to use a pivot table but reduce the data set by a user enter range of dates? The date field is the first column in my data sheet.
View Replies!
View Related
Formula For Calculating 360 Rowsx180 Columns Data Set
I have a set of data 361 rowsx 180 columns. I need to calculate a volume using these depth measurements and the one row of latitude. The formula i need to use is along the lines of =111120*cos(A1)*B1. Then for the next calculation A and B need to be the next cell down, so =111120*cos(A2)*B2. This continues all the way to the bottom and then the equation starts back at A1 for the cos() value and B changes to column C, so it will be =111120*cos(A2)*C2, then you go down the columns until you reach A180. this goes for 360 colums.
View Replies!
View Related
Compare Data-set With List & Summarize Matches
1) Background Info We are trying to summarise some data that has been exported from an ancient database into a poorly delimited csv file. My colleague has imported the csv file into Excel ( attached), and we are trying to work out what to do next. The dataset contains approx 300 records - each record being called a "sample" and having a unique sample number. NB: I have had to attach a cut-down version with only 3 samples, due to file-size - but it will hopefully give an idea. Each sample contains 2 types of information that we are interested in (and a lot of irrelevant data besides). The relevant bits are: - predicted occurrence of various species (given as a percentage for each species) - observed occurrence of various species (given as positive/negative for each species, where positive is indicated by an asterisk) 2) What We're Trying To Do We are trying to compare this dataset with a master-list of 80 species names. For each species on the master-list, we want to: a) check whether it has a "predicted occurrence" value in each of the 300 samples (and if so, record the value in a summary sheet) b) check whether it was observed in each of the 300 samples (and if so, record this in the summary sheet) 3) The Problem Unfortunately, the dataset has imported into Excel as a gigantic list: 48000 rows (including loads of blanks) and only 3 columns across. To find the relevant data, it's necessary to: a) open the attached workbook, and go to the worksheet named "Data" b) Scroll through the rows, looking for the string "RIVPA" in column A. This tells you where each new sample begins. (The sampleID is stored in the same row as this, in column C. It is mixed up with a load of text, which we will need to separate out at some point, but that's a secondary consideration at the moment.) c) Scroll down further until you find the text string "Predi" in column A. This indicates the beginning of the data we're interested in, for each sample (i.e. for sample 1, I'm talking about row 58). Count down a further 2 blank rows, and then you find the data itself: - Column A contains the observed occurrence (a positive result is indicated by an asterisk) - Column B contains the predicted occurrence - Column C contains the species name My colleague was trying to build a summary table, in the worksheet named "Summary". He was using the LOOKUP function to extract the data, but because there are so many samples, it's beyond unwieldy. He asked me if I could write a macro do do the job, and this is where I'm stuck. How could I set up a macro that can identify where one sample starts and finishes, and where the data is within each sample? What is the most sensible shape for such a macro? If I could get the overarching logic worked out, I could make a start on writing the individual bits of code, but at present I just can't figure out how to begin. To make things more difficult, the samples are not all the same length, and do not all contain the same list of species as each other. (However, at least there should be no species in the samples that are not in the master-list). The first sample begins on row 5, the second sample begins on row 173, the third on row 340, and so on.
View Replies!
View Related
VLOOKUP And SUM (search For A Specific Data Set And Add The Values)
I want to use vlookup up function to search for a specific data set. when it finds it i want it to look 2 columns over and add the value then continue to search for the specific data set again in the range provided in the vlookup, when it finds another matching the criteria look 2 columns over and add that cell value to the previous count. keep a running total until it has looked through the whole sheet. here is an example of the vlookup i am using, it is not suming because thats where im stuck. also a quick data set to picture what i am talking about. In the data set in plain terms i want to look for the line name and add the qty on that line into a chart. =IF(ISNA(VLOOKUP(A8, 'Web Queries'!G$3:H$40, 2, FALSE)) = TRUE, 0, VLOOKUP(A8, 'Web Queries'!G$3:H$40, 2, FALSE)) LineOrder_NumQTY 7L58545487320 7L5856060351 Z_56784817515 Z_56784824115 Z_57454577222 Z_57454562424 Z_57454547524......................
View Replies!
View Related
Macro To Extract Variable Info From Data Set And Calculate Corresponding Values
I have a small data set (ends up with 8 columns, up to 1000 rows), that gets imported sorted and formated via a macro linked to a button. I need to be able to look at one particular column of info, which will contain different text values every time the data is updated, and create a list of all the different values that occur in that column (maybe 10 max). Then...... I need to calculate a numerical value from adjascent column, linked to the text values from the first process. Below is an example to better explain:............
View Replies!
View Related
Open Set Of Files To Fetch Data Starting From A Master File Where The Links Are Given Using A Loop
I am trying to open set of excel files to fetch data starting from a master excel file where the links are given using a loop. Some of files given as links are either absent or the link is wrong. * What is the syntax to find if the link is correct/present * What is the syntax to find if the file is present in specified location * What is the syntax to find if the file is password protected to open * I use error handler to resume the next statement if OPEN statement fails.It works fine inside the loop only for first two files. For example if the third link is errorneous the error handler fails and RUN TIME ERROR occurs.
View Replies!
View Related
Matching Data In A Data Set
I am having problems with working out how to do a lookup / Match formula to specifically cross reference a row of numbers against each row of numbers in a data set, and to then return the values that match in the first row alongside that row of the data set. For example I would have 8 numbers in row 1, and a data set of 20 rows of 8 numbers starting at row 3. I want to do a formula that looks at the numbers in row 1 and if any of them show up in row 3, list each matching number in columns alongside the numbers in row 3. I have attached an example to further demonstrate what I am wanting to do.
View Replies!
View Related
Set Variable: Object Variable Or With Block Variable Not Set
I have the following code (just pasting the relevant section) which crashes when it reaches the highlighted line of code. and a dialog box pops up with the text: "Object variable or With block variable not set" Sub test() Dim StartRng As Range Dim Buffer As Range Set StartRng = WorkSheets("Sheet1"),Cells(1,1) StartRng.Activate ActiveCell. CurrentRegion.Select Buffer = rngStart.CurrentRegion.Copy ' I also tried the following line of code but that didn't work either 'Set Buffer = rngStart.CurrentRegion.Copy .. ... End Sub
View Replies!
View Related
Highest Value In A Column Using Set Criteria
I am tying to find a way that I can get the highest value in a column of data depending on a set criteria, I have used the following =MAX(IF($C2:$C$109=$C2,IF(CJ$1=K$1,IF($D2:$D$109=$D2,K$2:K$109,0)))) and it works for the first row, I can drag it across ok, but if I drag it down it just populates the same value as the cell the formula started in even though the IF logical test has changed?? Can you please Help fix or offer alternative. Also is it possible (say the above worked) to add to the formula so that it returns a value from another cell in the same row that it found the highest value? e.g. =MAX(IF($C2:$C$109=$C2,IF(CJ$1=K$1,IF($D2:$D$109=$D2,K$2:K$109,0)))) say this returned the value and it was found in K52 it would return me the value in B52
View Replies!
View Related
Variable To Set A Column Range
I am trying to set a range for a for next loop using the result of a find, the location of the find result will always be row 1 but may be in any column after Column D. I have been trying different ways to work out this issue but have failed in all my attempts. I would be very grateful to anyone who can tell me where I am going wrong (I am sure it will be very simple). For Each shtRegisterSheet In ActiveWorkbook. Sheets shtRegisterSheet.Activate Set rngFindBalance = shtRegisterSheet.Range("c1:da1").Find(what:="Balance") dteSaledate = Format(rngFindBalance.Offset(0, -1), "yy") dteYeardate = Format( Date, "yy") If dteSaledate < dteYeardate Then ......................
View Replies!
View Related
Set Range Depending On Value In Column D
I have a list of data in colum A and I want to group them together. In column D I number the group with the same number. A1 = Tony D1 = 1 A2 = Tom D2 = 1 A3 = Bill D3 = 1 A4 = Helen D4 = 2 A5 = Marg D5 = 2 A6 = Kathy D6 = 2 A7 = Catherine D7 = 2 And so on..... The list up to 100 names As you can see group 1 = 3 names, group 2 = 4 names, group 3 = 6 or maybe 7 and so on. I'm trying to set ranges for them Eg: set rng1 = A1:A3 set rng2 = A4:A7 but it not fixed to set ranges like that. It's variable and changes every week. Next week maybe rng1 = A1:A5, rng2 = A6:A9. can I have the VB code to determine these variable ranges.
View Replies!
View Related
How To Set A Column To The Same Date-format
I found this board with a question for a column change to a specific date-format. This is normally very easy but in this case not for me. The dates that are in the colums are in different shape! Here are some examples: 21-Jun-07 16:00 A ( where the A is set by another program ) 20-6-2007 11:00 18-Jun-07 08:00* ( where the * is set by another program ) The format I want is: 21-Jun-07 ( nothing more / nothing less )
View Replies!
View Related
|
TRACKER
