I have posted earlier thread on similar problem but i did not get much response.I am really having a tough time to record a macro, where my columns will be selected with an interval and then will be pasted in to another sheet....
I have a worksheet that contains data in the first three columns and then the 4th column is empty and then there's data in the next three columns and then an empty column etc. How can I cut the data from columns E to G and I to K and M to O etc....and paste it directly below the data in columns A to C? I dont know how many columns of data there are in the worksheet and every set of 3 columns of data (eg. E to G) varies in size. I recorded the following macro of what I want to do.
Sub rearrange() Range("E1:G1").Select Range(Selection, Selection.End(xlDown)).Select Selection.Cut Range("A95").Select ActiveSheet.Paste Range("I1:K1").Select Range(Selection, Selection.End(xlDown)).Select Selection.Cut Range("A203").Select ActiveSheet.Paste Range("M1:O1").Select Range(Selection, Selection.End(xlDown)).Select Selection.Cut Range("A386").Select ActiveSheet.Paste End Sub
Code copies the first two columns of a many column table and pastes them at a certain interval (14 columns) to make transfer to a report easy. The problem is that the worksheets each have a different number of columns, but none more than 56 columns. The macro works wonders on the first sheet, but thereafter does not work at all. The first sheet has 27 columns, the second sheet only has 4 columns and the one after has 38 or something.
Public iMaleGroup As Integer Public iFemaleGroup As Integer Public iMaleAnimal As Integer Public iFemaleAnimal As Integer Public iMaleGroup1 As Integer Public iFemaleGroup1 As Integer Public StudyTitle As String Public SmallAnimal As Boolean
Dim Wrkst As Worksheet Dim wsName As String Dim wsSubject As String Dim wsNumber As String Dim rSummaryHeader As Range Dim MergedHeader Dim NextMergedHeader Dim HeaderRange Dim AddHeader Dim TableHeader Dim SumTableHeader Dim PasteRange Dim x As Integer Dim n As Integer Dim z As Integer Dim i As Integer
Is there a function to convert the data shown in table 1 to table 2 without rearranging the columns and rows? because i don't want to use TRANSPOSE. I want a function, somthing like SUMIF with OFFSET or INDEX and MATCH or any other function.
I have a list of names in the format "SURNAME, Firstname". By using left find and mid find formulas and then concatonating the result I can put the first name first and the surname after it in proper case. This works well for the most part (although if there is an easier way I'd be happy to hear it!) except for when it comes to people with double unhyphonated surnames - lets say for example Ella Van Hamburg.
The name would come through in the raw data as VAN HAMBURG, Ella When I separate the surname and change the case it says Van Hamburg But when I go to extract the first name I get the result HAMBURG, Ella And so when I concatonate: HAMBURG, Ella Van Hamburg. It works perfectly for everyone else, and with hyphonated surnames. I am then using this data in a VLOOKUP which means that the final result has to be in the leftmost column so at the moment that's just where I'm concatonating it.
I imported a file of National Historic sights which imported fine with the exception that it's all out of order. Here is how it currently looks:
ResnameFIELDFIELDVALUE Lake Norconian ClubApplicable CriterionARCHITECTURE/ENGINEERING Lake Norconian ClubApplicable CriterionEVENT Lake Norconian ClubArchitectGibbs, Dwight Lake Norconian ClubArchitectWilson, G. Stanley Lake Norconian ClubArchitectural StyleMISSION/SPANISH REVIVAL Lake Norconian ClubArea of SignificanceARCHITECTURE Lake Norconian ClubArea of SignificanceEXPLORATION/SETTLEMENT Lake Norconian ClubCurrent FunctionDEFENSE Lake Norconian ClubCurrent FunctionGOVERNMENT Lake Norconian ClubCurrent SubfunctionCORRECTIONAL FACILITY Lake Norconian ClubCurrent SubfunctionNAVAL FACILITY Lake Norconian ClubFederal AgencyDEPARTMENT OF THE NAVY Lake Norconian ClubHistoric FunctionCOMMERCE/TRADE Lake Norconian ClubHistoric FunctionDOMESTIC..........................
how to link cells such as one cell with a date and an adjacent cell with data so that while I am rearranging the data in various ways the date that corresponds with the data stays with it as it moves around while I am sorting the data.
I have a csv file that includes several runs of a test per day on 5 different parameters. I need to rearrange this data to show the results from each run on each date, but arranged in a different way. I've attached an .xlsx file that best describes what I have
I've just exported a list from SharePoint to Excel, which gave me as a result a owssvr table, so far so good cause every time the list in SP is updated I can do a refresh and get the latest values; however once I opened the table I realized that the columns where not correctly order they were all mixed and the information does not look as it is required so I need to rearrange them. I do not need to delete a column I just need to be able to move them between themselves so that they will follow certain order: Product ID, Name, Amount of pieces, Place where they are stored, etc. this is very important because later on I use "vlookups" to do a series of reports.
I've tried cut- paste to move the columns to the correct position, unfortunately once I close the excel file and try to open it again, I got a message saying that the content is not readable and when Excel repairs it, my owssvr table loses the link to SP so I cannot update refresh the table anymore.
I cannot edit the list in SP as this site does not belong to me and I only got access to export the data and be able to refresh the table, all I want is to be able to move them within my ovssvr table so that locally I can work with them better.
Here is a pic of what I am talking about: cmms.JPG
So for example in the pic I put, I need that instead of Comments in Column E, Product ID can be in Column E, then Name in column F and so on..
rearragning text on excel. I believe Macro will be needed to solve this problem so if anyone can help me out, it will be a big help. Thank you.
Example 1: I need to rearrange "trace Silt, cm SAND, some- f Gravel" into "cm SAND, some- f Gravel, trace Silt"
Example 2: I need to rearrange "some+ Silt, f SAND, trace- f Gravel" into "f SAND, some+ Silt, trace- f Gravel"
There are many different cases for this problem. So if there is a way for the computer to recognize the Capitalized word and move it to the front and rest should be arange in order listed below.
Order and+ and and- some+ some some- little+ little little- trace+ trace trace-
each term is seprarated by commas. For example f SAND, some+ Silt, trace- f Gravel each color represent one complete sentence. and the order for them should be capitalized funciton first, then the follow the order provided above.
I have a set of related variables that are split over multiple worksheets, and I need to be able to take specific information, duplicate certain values and produce an output sheet for use in a separate piece of software.
The variables are: Position Number (Sheet 1) Position Title (Sheets 1 and 2) Position Requirement (Sheet 2) Requirement Importance (Sheet 2)
The output sheet requires a list of all the requirements for each position number, which means the position number itself needs duplicating (in new rows) X number of times, where X is the number of requirements assigned. The appropriate requirements are then to be pasted in next to each position number (and the requirements can be found by comparing position number to title, and from title to requirements).
Normally, Iíd be able to do this using lookups and so forth, but my problem arises when I have multiple position numbers with the same related title (in the attached example, there are three plumbers with unique position numbers). I canít figure out how to say to Excel ďa plumber has five requirements, and there are three plumbers, so duplicate each position number for each plumber five times, then insert the appropriate qualifications (and their associated importance values) next to the position numbersĒ.
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.
I think I've had enough of using excel generated macro's as an excuse for decent code. I need to turn this:
Small procedure, function, what have you that will continue this pattern until I hit some kind of indicator to tell it to stop. This just clears the contents from 7 rows, hops down 9 rows and does it again.
I would like to write a formula to average cells F27 - F11007 in a spreadsheet for every 96 values. That is, I want the average of F27:F122, F123:F218, F219:F314 and so on up to F10912:F11007. Ideally I would be able to pull down the fill handle and perpetuate the formula since this is a lot of data.
I've been trying to use INDIRECT and OFFSET formulas but I get a #REF! or #VALUE error.