I'm trying to work out if there is a way to rearrange the contents of a cell. Basically, I have names in each cell which have surname then first name and I want to have them reversed.
I know it would be easy if they were in seperate cells but unfortunately that's not the case. Is there a way to do it? If it's any help surnames are in upper case and first name in proper case.
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 attached a workbook stating my problem.
file1.xlsx‎
I thought this was a pretty simple formula but I am having difficulty creating it. I am attaching a little test spreadsheet. Sheet 1 is where the data will be entered. The Reimbursed column has a drop down choice of yes or no. The next 2 columns are the cost of registration and the cost of accommodations. On sheet 2 is where I would like the formulas. So in cell A4 I would like a formula that says if B3 on sheet 1 is Yes populate this cell with the contents of Cell C3 only, B4 of sheet 2 would then be B3 if A3 on sheet 3 is Yes and so on with the Not reinbursed if sheet 1 the Reimbursed column is no.
View 3 Replies View RelatedI 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 want to rearranging the data in year wise for each company. My data (Sheet 1) is in this order.
Year Company A Company B
1996 Data 1 Data 1
.......
2006 Data 11 Data 2
I wan to rearrange the data (Sheet 2) in to the following order.
Company A 1996 Data 1
........
2006 Data 11
Company B 1996 Data 1
.........
2006 Data 11
Is there any possibility for vba for this soloution. If i will increase the no of companies, whether the vba will work or not.
I have attached a file for the same
I have about 20 columns headed things like Product Number, Size, Colour, Weight etc
So for example, one record might be something like:
Product number
Size
Colour
Weight
Col5
Col6
Col7
Col8
etc
[Code]....
I have about 10,000 of these records.
What I need is to rearrange them in another sheet which just has 3 columns.
Product number
Attributes
Values
A record would look something like
Product number
Attributes
Values
[Code]....
Is it possible to transform the first format in the first spread sheet to the second format in the second sheet?
I have table with duplicates values on the left side. Table has three columns and in each row is value only in one column. It looks like this:
EVI ROZ POA
101 22
101 3
110 6
110 2
110 3
112 12
114 8
114 6
...
I need to get rid of the duplicates in first column and save values for same number in first column in one row. Like this:
EVI ROZ POA
101 22 3
110 6 3 2
112 12
114 8 6
...
I think only macro can do this.
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....
View 6 Replies View RelatedI 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.
View 2 Replies View RelatedI 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
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
View 8 Replies View RelatedI have a data table which looks like this:
1 2 3 4 a b c
5 6 7 8 d e f
And it should be converted to look like this:
1 2 3 4 a
1 2 3 4 b
1 2 3 4 c
5 6 7 8 d
5 6 7 8 e
5 6 7 8 f
So basically, the data in the last columns should be transposed, and the data in the first columns need to be copied in the cells of the new rows.
unable to find anything besides splitting numbers and letters in a column into multiple columns.
The raw data at the moment looks like this:
ID Number
Ticket Number
Event
Sheet1 *A158662TIC-00013Food4flags512346TIC-00027flags8picnic9555510TIC-000311Food12picnic139707414TIC-000415Food16picnic17784218TIC-000519Everything
I would like to somehow change that into this:
Sheet1 *ABCDE15866TIC-0001Foodflags*21234TIC-0002*flagspicnic35555TIC-0003Food*picnic497074TIC-0004Foodpicnic*57842TIC-0005Foodflagspicnic
How do I rearrange a list of data in one column that are in sequence to appear at random i.e data should not follow any particular pattern.
View 4 Replies View RelatedI'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..
Formula that can do this?
Sheet1
ABC111213214325436547658769871018112123113421453156416151726183194120522163227423852496257268279
Excel 2007
I basically want column A to be like Column C. The logic is that every time the row that have 1, skip a row and run the numbers until the next 1 appears.
Is there a simple way via VBA to alter the layout of data from a mixed up two column list into multiple headed lists on another worksheet (within the same workbook)?
The attachment should better demonstrate what I mean. Sheet1 has example data of how it is and Sheet2 shows how I would like it.
The data will be dynamic in the sense the numbers of unique values in column A will change (only increase, never decrease), as will the number of unique values in column B.
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 have 2 inventory reports: what my store has and what my supplier has. I need to copy Tab1:K# to Tab2:T# provided that Tab1:A#'s contents match Tab2:A#'s contents. The A column represents the SKU of the item, but there is a difference in the amount of SKU's in each (my store sells ~6,000 items, supplier has ~10,000 items), so it's not as simple as sort by column A and copy pasting column K to column T.
For instance:
On Tab 1, A2's value is [1], K2's value is [9.38].
On Tab 2, A70's value is [1], K70's value is blank, but I need it to be [9.38], to match Tab 1's respective SKU.
I almost thought I had it figured out with VLOOKUP, but I cant seem to get it right... It doesn't reference the correct number.
Screenshots for reference
First tab, from the wholesaler: [URL]
Second tab, store's stock: [URL]
I am in the process of reformatting an excel workbook to act as a review tool for different factors in a process. Part of my redesign includes the use of coding that creates different cell colors based upon the cell contents. The new workbooks will be used to handle existing data for this year. I have developed a process macro to open an existing workbook and copy and transfer the original data worksheet into the newly formatted workbook. The data gets transferred to the new worksheet and the resulting workbook is renamed and saved, Heres'' the rub... the newly saved workbook is missing all of the coding for the worksheets... apparently this is a MS bug.
Has anyone figured a workaround for this. One thought I had is to open both( new and old )workbooks and rather than move/copy , i would transfer the data using cell references.
Been racking brain, searching through the forum here, and my Excel 2003 Bible all day trying to figure out this problem to no avail. I would like to clear the contents of any cell in a given range if the cell immediately to the right of is formatted as bold.
View 2 Replies View RelatedI get a report each day with a list of issues. the "group" that works the issue and the "priority". Based on these two factors, i need to do a double lookup (vlookup?) to another tab or file to match the priority and group and see what value should be brought back for each lines results. For example, if group1 had a prority3 issue, the lookup would find the value from the other sheet or file and bring back the value and put it at the end of the row where the formula is.
Attached are examples of the sheets.
sheet1.jpg
sheet2.PNG
we have a Excel 2010 worksheet containing multiple tables.
Table1
ColumnA ColumnB
1 Blue 12
2 Orange 14
15
11
3 Red 10
Table2
ColumnA ColumnB
1 Blue 11
2 Black 13
15
11
3 White 10
19
17
On a separate worksheet we want to calculate with the first worksheet's values.
For example: Calc1: Sum ColumnB IN Table1 WHERE ColumnA = Orange
How do we do such a calculation/formula?
i need a code that moves down a column and for every empty cell in the column the cell to the left is cleared and then it moves on to the next cell down. the column is not always the same and will start from a selected cell, and the column will contain no more than 5 rows
View 3 Replies View RelatedI am trying to find some VBA that will allow protected cells to be updated when a user enters or selects data in other cells.
For example:
Cell 'A1' starts out blank.
Cell 'B1' has a drop down to select specific items.
Cell 'C1' is the date of the order and is a locked cell.
Cell 'D1' is the date of completion and is a locked cell.
The worksheet is protected to prevent direct changes to cells C1 and D1.
If a user enters any information in A1, then C1 displays the current date.
If a user selects 'Complete' from the drop-down list in B1, then D1 displays the current date. Both of these actions are independant of one another. This format is the same for every cell in the 4 columns indicated above. I thought I was able to do this in another spreadsheet I created a year or so ago but I have not been able to figure out what I did and I do not have the spreadsheet to look at.
I am working with a spreadsheet that has our user data; User(First) Name, User(Last) name, Computer Name, MAC address, Phone Number, Seating Position and Network Port Number.
I have let this get horribly out of sync, moved computers around, and moved them while the MAC address column was hidden, As a result, I have a bunch of users listed as being at the wrong computer. I have a second spreadsheet I generated that gives me the logged in user for about 2/3 of my computers, with the computer name(Just 2 columns, 'A' and 'B'. I would like to have Excel match the computer name and then overwrite the user name. For example, if the first computer in my correct user list is DELL-99945ty2, and the user name is "Jimbo Jones", I would like it to search the first Excel doc(The one with all of the user data), and replace the user name with "Jimbo Jones". Is this simple and straightforward?
In my main spreadsheet, the user name is in column B and the computer name is in column G, So I want to use the values from Column B in my second document to find its match in Column G of the first spreadsheet, and then replace the data in Column B with Column A in the first spreadsheet.
How to Lock/Unlock an Excel Cell Based on the Contents of Another Cell?
View 1 Replies View RelatedI'm trying to set up a dependent cell (B) based on what is in the independent cell (A)
If A is blank, B should be blank
If A has any value other than X, B should display a set string Y
If A contains X, B should contain a drop down list with a range of choices.
I've tried setting it up as a dependent validation cell and I've tried using OFFSET, but the best I can do is to get three different drop down lists to populate cell B. One with only string Y, one with only a blank, and one with the range of choices. The problem is in the first two cases above, I want the cell to fill automatically with either blank or string Y -- I don't want them to just be drop down options, with the cell displaying the last value that was input.
