I have essentially the same issue with my spreadsheet, but mine is a little easier. I have written code to do the first few steps. I could just use some assistance with the middle. I know my thought on the process is "the long way", but with my experience in VBA I have to keep it simple. Here is my data and then the way I want it to look. I can add a macro to make it pretty afterwards, unless your way is easier (and I understand what is happening). --Oh, I am trying to automate my inventory process.
I have a spreadsheet and i wish to move every nth cell in column A to the initial cells in the row.
example:
Cell A2 moves to E1
Cell A3 moves to F1
Until the end and then select all and sort by column B (this would get rid of blanks and sort everything) before moving to the pretty stage.
How the data comes in. And my Final Goal.
This document is several thousand lines long. I made up the data to protect the weak. There can be duplicate usernames and vendors, but never serial numbers and computer names. Duplicates should be highlighted to be found easily and researched.
I only need to get to the middle picture. After that I can modify with what I come up with.
I have a table listing certain values (Column C) and their respective probabilities, based on normal distribution (Column D). As these probabilities are dependant only on values from Column C, I'd like to list all the values from that column into another fragment of the sheet, say F6 and below, but each value should appear there only once, no matter how often it occurs in its original Column C (I listed them manually in Column F in the enclosed example). Moreover, if a new value appears in Column C, it should be also included in the new place and sorted in increasing order.
I've got this large data set that I need to edit. My problem is, the clients name and address are contained with in one cell eg, John Smith, Alba Road, Bigtown. Is there some way of transferring this address into three different cells foe filtering?
I am working with a formula that has multiple embedded IFs and therefore references the same cell multiple times. However I am also copying it between workbooks for different companies so some of the information changes which column it is in. For example, there is a formula in AA5 that has 3 references to R4 and 4 to R5 and I need all of the R4 references to be T4 and all of the R5 references to be T5.
I can't do a "find and replace" because the formula in Y5 still needs to reference R4 and R5.
I have been clicking on R4 and dragging it 3 times to T4 (and the same for R5 to T5). It just seems like there should be a way to move all of the R4 references at one time to T4.
Is it possible to move rows of data in a spreadsheet to multiple spreadsheet accordingly? I had lists of tasks in a single spreadsheet and i need to segregate the tasks for all my staff in serial while no duplication among all of them. For instance, i got 4 personals in my department and i need the 1st 4 tasks to be distribute to each of them and next 4 tasks accordingly. This is due to all tasks are equip with due date and i need to calculate how much time i need to accomplishing them. i'm used to manually move it and found it time consuming, so i was wondering if someone would instruct me where or how to achieve it by using a simple macro.
can i make it so i move any of the cells up or down a row or wherever and they will all move in a unit as if they were merged? i dont want to merge though because then that will screw up the data?
I have an excel work book with 6 tabs. I would like to have Excel move an entire row from one tab to another tab (removing the row and inserting it in the other tab). I.e. Example I have a tab with items that are marked as "Open Actions" so if I were to change the drop down to close. Excel would move that entire row of actions to the tab with the "closed actions" and insert into the next available row. Now if someone were to come back at a later date say no it should be reopened than I would change the drop down to open and excel would move that row back to the open actions tab into next available row. I tried a PIVOT table and no good I played with few macro and not.
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?
I'm working on a complex spreadsheet and I'm working on a complex spreadsheet system for pulling and measuring data. My VB programming skills are about minimal/average, so you may see me on here asking various questions . In any case, what I'm trying to do is create isolation macros for "Kickback" data (erroneous). I'm trying to remove data with certain criteria and isolate it on a separate "kickback" sheet for one for taking a second look at. I've made the easy macro of creating a new spreadsheet:
Sub Create_Kicbacks_Sheet() ' Create_Kicbacks_Sheet Macro ' Creates "Kickbacks" sheet for invalid information. Sheets.Add After:=Sheets(Sheets.Count) Sheets("Sheet4").Select Sheets("Sheet4").Name = "Kickbacks" Sheets("Kickbacks").Select End Sub
This coding works correctly. The problem area I'm finding is the sorting data. My goal is to look at Columns A and B for certain criteria and either leave it alone, move it to the "Kickbacks" sheet or delete (due to not being necessary in data calculations). Basically, here's a synopsis of what I'm looking for:
if Column A = Y and Column B = Mandatory -> Leave Alone if Column A = Y and Column B = Best Efforts -> Move Row to Kickbacks if Column A = Y and Column B = Empty Cell -> Move Row to Kickbacks if Column A = Empty Cell and Column B = Mandatory -> Move Row to Kickbacks if Column A = Empty Cell and Column B = Best Efforts -> Delete Row
Here's the code I have in excel (modified from one I found online)... Which only is doing some of what I want it to do:
Sub Moveto_Kickbacks() Dim r As Range, LR As Long With Sheets("Data") LR = .Range("A" & Rows.Count).End(xlUp).Row Set r = .Range("A2").Resize(LR - 1) .Range("A1").AutoFilter field:=1, Criteria1:="" .Range("B1").AutoFilter field:=2, Criteria1:="Mandatory"
I have the following code that works properly, Copy & Paste Same Range On Protected Sheet From Multiple Workbooks
Sub RunCodeOnAllXLSFiles() Dim lCount As Long Dim wbResults As Workbook Dim wbCodeBook As Workbook, book1R As Range Application. ScreenUpdating = False Application.DisplayAlerts = False Application.EnableEvents = False On Error Resume Next Set wbCodeBook = ThisWorkbook With Application.FileSearch .NewSearch 'Change path to suit...............
I'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?
I'm trying to determine how much time our agents are spending for their lunch/breaks during 30 minute intervals. Range A8:A200 lists the agent's names, Range B8:B200 lists their lunch/break start times, Range C8:C200 lists their lunch/break end times, and Column E lists the times (8:00, 8:30, 9:00, etc.). If an agent starts their break at 10:57 (Column B) and ends at 11:10 (Column C), the value next to 10:30 (Column E) would have 0:03:00 and the value next to 11:00 would have 0:10:00. But would need to sum all the agents which took a break/lunch between 10:30-11:00, and 11:00-11:30.
In this workbook Test1.xlsm under column "D" i have Po numbers. Some PO numbers are the same and some aren't. I have a specific code that creates an invoice for a customer when i hit the code however i must select a cell under column "D" in order for the code to create an invoice for the customer on that row. I usually create the invoice for all the customers of the same PO number. Sometimes the amount of invoices i have to create is overwhelming so i was wondering if it's possible that when i select a cell under column "D" that has, let's say, PO number "654" if the code can create an invoice for all customers that have the same PO number of "654"?
I have a excel sheet with several columns and 2700 numbers in each column. In the first column there is standing from witch quarter to witch quarter of the day the values are (and this for several days in one sheet). I want to take the average of all the first quarters of the day, the average of all the second quarters of a day,.... But if I would do that by just sorting my table on the quarters and than manually make the formullas I would need to give in 800 formules. (way to much)
So I would like to find an easier way to take the average of column B for the valeus were A is "00:00 -> 00:15" (this are avery time 28 numbers in the month february)
In attachement you can find an example of my problem, although it isn't with real values.
I have 2 columns with data in them, basically representing a gaussian distribution. Column A has the "X-axis" values and so is uniformly ascending with no duplicates. Column B has the "Y-axis" values and increases up to a maximum and then decreases again (this is data from an instrument and so its not completely smooth but is close). An example is below.
0 4 1 8 2 16 3 27 4 50 5 27 6 16 7 8 8 4
What I would like to do is get the 2 Column A values where the corresponding column B value is half of the max (in the case above, 25 is not available so the closest is 27). I am trying to calculate the difference between these values, so in the example, I would have 5-3. Is there a way to do this?
ok is there a way to have excel fill a certain number of cells with specific data specified X times? For example, the end result would look something like the pic below. The column on the right would change according to the numbers specified in the yellow column.
I currently have two lists I am working with. One is a big mix of many different names, names which all need to be replaced.
I have another list which has all of these original names included in one column and the names which should replace those names in the column next to it.
Is there a way to go down this list and have excel find and replace every name in the sheet with its corresponding name without having to do it manually over and over?
I've got code that loops through approximately 700 excel files and unprotects the workbook. I have to run this code for five consecutive days, and the issue I'm having is for the files that are unprotected on Day 1, the code still runs for those files the next 4 days.
There may be only 10-15 new files submitted on day 5, but the code still runs through all 700.
I have a problem with multiple charts in one sheet. The problem is really weird because when i add the charts to the sheet where all the values are then there is no problem, but when i set the position of the charts to another sheet and specific position it gives me an error
Here is my sub for making the chart:
Sub chartFormat(FChart As Chart, Hø As Integer, Bre As Integer) With FChart .ChartType = xlLine With .Parent .Height = Hø .Width = Bre End With With .SeriesCollection(1) ' THIS IS WHERE THE ERROR OCCURS .Border.ColorIndex = 10 .Border.Weight = xlThick End With With .Axes(xlCategory) With .TickLabels .Alignment = xlCenter .ReadingOrder = xlContext .Orientation = xlUpward End With End With
are the sheets where I wanna place the chart and where I get the data.
Now if I change profVis to sag all places then it works fine and it places all the charts and make them perfect. But when profVis is there, it makes and error 1004 :
Method 'SeriesCollection' of object '_Chart' failed.
But the error first occurs after the first 2 chart have been made. I've tried switching them, so the last chart becomes the second, and it can make it. It seems as if there is a limit of 2 chart?
I am trying to stream line the recording of time tickets . I would like to create a Summary sheet that shows the amount of hours each worker has done based on that particular day. The trick is I want the summary sheet to be automatically updated when the each individual time ticket sheet is completed. I would also like to be able to drag the formula across a row so I do not have to recreate the equation each time a new time ticket sheet is completed. Each time ticket sheet is its own sheet and is identified by date. Each time ticket ticket sheet is identical and is already created to so all you have to do is click on that the sheet reflecting the date and fill in the information.
This is the equation I have created but keep getting #VALUE!
The sum_range is Manhours from L11:L34 for each time ticket sheet. The criteria_range1 is date in cell B6 listed on each time ticket sheet, criteria1 is date listed on cell J8 of the summary sheet . The criteria_range2 is names from C11:C34 for each time ticket sheet, criteria2 is name of person on cell I9 of the summary sheet.
Each sheet is labeled as 8-19-13, 8-20-13, 8-21-13, and so on. Summary sheet is labeled as Summary
The table that I am using to record the data on the summary sheet has the date increasing by one day on row 8. The names are vertically listed on column I.
Basically on the file below whenever there is data in column "type" I want excel to copy the "name" and "account" next to it. The whole file has about 80,000 rows. Spacing is not always one blank row between accounts. I am using excel 2007.
I used this method to hide previously selected values in a drop down menu. I thought by including that value multiple times in my "employee" list, I could use it multiple times before hiding it. Is there any way to skirt this? I need to use each value three times before it disappears.
I have a sorting question in Excel 2010. Attached is an example workbook with a simplified version of the situation.
I want to sort a table multiple times. I have a table with part numbers and alphanumeric locations (Row, Shelf, Bay, Slot). I have formulas that divide up the location into 4 separate columns to be able to sort.
The first sort I do is by location, which I can easily achieve. The issue I have is sorting AGAIN by part number, while keeping the original sort somewhat intact. If a part number shows up multiple times (i.e. in two DIFFERENT locations), the Nth instance might show somewhere down the list.
Is there a way to sort my table to where you keep it in location order WHILE accounting for duplicates, which I would want grouped together in location order? Please see attached file : SortExample.xlsx
