Truncate To Last Common Folder In A Column?
Sep 15, 2014
I have Column A with file paths from the same main directory.
C:UsersSJohn.AJAXDesktopTimeBills.docx
C:UsersSJohn.AJAXDesktopTimeDChargers hick.docx
C:UsersSJohn.AJAXDesktopTimeDChargers raps.docx
C:UsersSJohn.AJAXDesktopTimeDChargersDodgersstadium.docx
C:UsersSJohn.AJAXDesktopTimeDChargersDodgersstats.docx
I would like to find the last common folder along the entire Column (Time),
remove all to the left, and insert E: in its place. Getting:
E:TimeBills.docx
E:TimeDChargers hick.docx
E:TimeDChargers raps.doc
E:TimeDChargersDodgersstadium.docx
E:TimeDChargersDodgersstats.docx
I have already created a VBA Script for other parts of this project, so I would like to add the script to the end rather than create a separate macro. If there is a formula that can do this I am willing to work with that as well.
View 4 Replies
ADVERTISEMENT
Aug 22, 2014
I have two columns(1 and 2), Column two will for each data set have two common values. For most of the data set where column two have the second common value, i can find it based on column 1, but where column 1 differs, i do not know how to get data based on column two.
See attached, column 1 and 2 data. Column 3 I took out one common value, column 4 I just did a IF function to populate blank spaces with common value, but for row 24 - 26 if does not work because column 1 does not follow same logic. Is there a way to populate a third column with one value(can be either of the two common values in column 2(note there will be multiple data sets)?
doc list.xlsx
View 5 Replies
View Related
Oct 17, 2008
I have two lists, let's say:
A B
Eva 1982
Anna 1981
Lisa 1983
John 1980
Steve 1972
...
And
A B
Eva Female
John Male
Lisa Female
...
Can in any way match the above lists automaticly, so the information from List number 2, column B, appears at the correct place in List number 1, Column C? For empty cells, no information is fine.
View 3 Replies
View Related
Sep 18, 2013
I have a column in excel sheet of more than 1000 different names. I want to add S/o with each name how can I do that ?
View 4 Replies
View Related
Oct 12, 2009
In column A I have 50,000 cells, each containing 1 to 10 keywords. For example
A1 = "jobs"
A2 = "jobs in milton keynes"
A3 = "it jobs in milton keynes"
A4 = "sales jobs in milton keynes"
A5 = "well paying brickie work in spain"
etc
etc
At first I was trying to find out the most common keywords in column A, and I used the following code to do so
View 7 Replies
View Related
Jul 7, 2011
Table 1
Code:
7/4/2011AMWG16=01BLK25K_USA[a3]*042711Date a Rich Black Man By June 232350$0.19 http://www.jyxsaw.com/blackrm/index.php?t202id=2676&t202kw=AMWG16=01_0210-3
7/4/2011AMWG16=01BLK25K_USA[a3]*042711Date a Rich Black Man By June
[Code]....
I've got two tables of information here, if you scroll to the far right of table 1 and look at the left of table two, you'll see a common code like:
AMWG16=01_0211-20
AMWG16=01_0212-16
and these entries will appear multiple times as well
AMWG16=01_0212-16
appears twice and the data is different and will need to be added up
How can I:
1. Add up the data on each table with the common string such as "AMWG16=01_0212-16"
2. Take all the data that has been added up and combine the statistics, for example,
when its all done it should show
AMWG16=01_0212-16
in the first column, and then all the statistics from the two tables in the columns to the right.
I'm guesssing...
Since some entries appear multiple times, you will need to sum up all the unique entries for that value. Say:
AMWG16=01_0211-20
Shows up three times, you'll need to sum up all the values and this needs to be done on both tables
THEN
You can combine the data
I'm familiar w/ vlookup, index, match functions, but I'm not quite sure how to put this all together
View 4 Replies
View Related
Nov 1, 2013
I'm applying a filter in a sheet that's something like this, after applying the filter it looks like this (Example Item 999)
Item
Inv. Qty
Avi. Qty
Flag
BOX
999
12
9
N
X1
[code]...
I was trying to get the "Flag" Column value, (inside the filter and it crashed)
Code:
With InventorySheet
.AutoFilterMode = False
LRowOnQ = .Cells(Rows.Count, "Q").End(xlUp).Row
.Range("B1").AutoFilter Field:=2, Criteria1:=Project
.Range("D1").AutoFilter Field:=4, Criteria1:=ContractNumber
.Range("N1").AutoFilter Field:=14, Criteria1:=Code
.Range("Q1").AutoFilter Field:=17, Criteria1:=">0"
Set rangeFilteredInventory = .Range("Q1:Q" & LRowOnQ)
ControlFlag = .Range("L2").Value
End With
After the filter, it always take the same value.
Code:
With InventorySheet
.AutoFilterMode = False
LRowOnQ = .Cells(Rows.Count, "Q").End(xlUp).Row
.Range("B1").AutoFilter Field:=2, Criteria1:=Project
.Range("D1").AutoFilter Field:=4, Criteria1:=ContractNumber
.Range("N1").AutoFilter Field:=14, Criteria1:=Code
.Range("Q1").AutoFilter Field:=17, Criteria1:=">0"
Set rangeFilteredInventory = .Range("Q1:Q" & LRowOnQ)
End With
ControlFlag = InventorySheet.Range("L2").Value
Is there any way to take the most common letter of the result range?
I'm aware there's a average option in subtotal, but I think it's just for numbers.
Code:
With InventorySheet
.AutoFilterMode = False
LRowOnQ = .Cells(Rows.Count, "Q").End(xlUp).Row
.Range("B1").AutoFilter Field:=2, Criteria1:=Project
.Range("D1").AutoFilter Field:=4, Criteria1:=ContractNumber
.Range("N1").AutoFilter Field:=14, Criteria1:=Code
.Range("Q1").AutoFilter Field:=17, Criteria1:=">0"
[code]...
And it doesn't even do the loop.
View 1 Replies
View Related
Dec 30, 2008
I have a matrix with info of persons of different cities in consecutive rows, where the
city data is in column B. I would like to eliminate columnb B and divide all persons from
any particular city with 2 new rows, their city name and below a field for every column data.
*The number of employees for city would be variable.
Original data (The columns used go from A to E)
1 City_1 Employee_1 Address_Employee_1 Other data_Employee_1
2 City_1 Employee_2 Address_Employee_2 Other data_Employee_2
3 City_1 Employee_3 Address_Employee_3 Other data_Employee_3
4 City_1 Employee_4 Address_Employee_4 Other data_Employee_4
5 City_2 Employee_1 Address_Employee_1 Other data_Employee_1
6 City_2 Employee_2 Address_Employee_2 Other data_Employee_2 ...
View 9 Replies
View Related
Jun 4, 2013
I have a spreadsheet with approx 7000 rows, many of which contain the same item but with flavors and other variations on the end. An example would be:
VB:
A B
10142 6kg of whey bundle With Free protein shaker-Banana
10143 6kg of whey bundle With Free protein shaker-Chocolate
10144 6kg of whey bundle With Free protein shaker-Strawberry
10145 6kg of whey bundle With Free protein shaker-Unflavoured
10010 **Bodybuilding Warehouse Premium Whey Probiotic - 2.2kg
10011 **Bodybuilding Warehouse Premium Whey Probiotic - 2.2kg + FREE Shaker
Would it be possible To create a New column (column C) which would display all common words from row b into the New column Like below?
A B C
10142 6kg of whey bundle With Free protein shaker-Banana 6kg of whey bundle With Free protein shaker
10143 6kg of whey bundle With Free protein shaker-Chocolate 6kg of whey bundle With Free protein shaker
10144 6kg of whey bundle With Free protein shaker-Strawberry 6kg of whey bundle With Free protein shaker
[Code] ....
I've attached a larger sample of our list to get a better idea of different variations that are on the spreadsheet.
I think what we need is something similar to this thread[URL] .....
Sample List.xlsx
View 2 Replies
View Related
Apr 23, 2013
I'm using excel 2008 for mac. I've got a spreadsheet with around 4000 rows. The data is organized like this:
|___________URL_________|______EMAIL______|
|Example Domain
|Example Domain
|Example Domain
|Example Domain
|Example Domain
What I'm trying to do is concatenate all of the URLs into one cell so that each email address has only 1 single row with many URLs associated with it. I tried using VLOOKUP(B1, $A$1:$B$4000, 1, FALSE) but this didn't work. I thought it was a long shot anyway. I also tried writing a bash script do parse a csv with this data and create a csv file with the URLs concatenated with no luck. I'm trying to get my data to look like this:
|___________URL_________|______EMAIL______|
|Example Domain
|Example Domain
|Example Domain
|Example Domain
|Example Domain
Where these are 2 adjacent cells.
EDIT: The format didn't turn out like I'd hoped. In the first example, each URL has an associated email address, each in its own row. In the second, it is just one row, with multiple urls in the first cell and an email address in the second cell.
View 3 Replies
View Related
May 13, 2006
I have two worksheets. One has columns A B C , the second has columns A B C D. Columns A in the two worksheets reflect the same data (serial number) , although one worksheet could have more rows than the other since it gets updated manually daily. I would like to use a macro that would:
1. use columns A's value in worksheet 1 to match a row in worksheet 2 then merge columns from worksheet 1 & 2 and insert into a new worksheet
2. repreat until all rows in worksheet 1 are read
3. save worksheet 3
View 3 Replies
View Related
Sep 15, 2013
I have a couple of spreadsheets. all of them have a same or common column called "ID".
The order of the ID columns of the spreadsheets are not in the same order however.
I'd like to see if there is a way that I can merge the spreadsheets to one using the shared ID.
To explain more:
Spreadsheet1 columns are: "ID", "URL", "Location"
Spreadsheet2 columns are: "ID", "Picture"
Spreadsheet3 columns are: "ID", "Address"
As I said copy/past is not an option as the IDs are not in the same order. I need to create a new spreadsheeet and have all "ID", "URL", "Location", "Picture", "Address" in that spreadsheet.
View 12 Replies
View Related
Dec 21, 2013
I have an Excel file in which I want to find the numbers which are repeated in several columns (B to L), but there are too many rows to find them looking one by one cell, is there a function which will allow me to find the common values which appear in these columns?
eg.:
A B C
1 3 4
2 1 7
2 4 7
4 1 1
results: 1 & 4
View 6 Replies
View Related
Apr 1, 2014
I've been racking my brains trying to find a way of doing this. I have a list (column A in Excel) of over 50,000 organisations and I'd like to know what the most common words used in the names are. Ideally it would great if I could produce a top 10 list of the most common words at the top e.g. Ltd, School or Church with a count in the next column of how times that word it appears
View 5 Replies
View Related
Jun 23, 2014
I want to truncate the decimals like below: i.e.
2.22939393 -> 2.229
2.22977777 -> 2.229
3.33477 --> 3.334
1.062578 --->1.062
[Code] .....
View 5 Replies
View Related
Jan 7, 2008
I'm trying to truncate (not round) a percentage, for example if 6.25% to 6.2%. I'm having issues figuring this out because the percentage already in the cell is a result of a formula (i.e. SUM=A1/B1.) Does anybody know a way to acheive this without having to sacrifice the formula already in the cell?
View 5 Replies
View Related
Oct 14, 2011
I have many different numbers that are sperated by dashes
example
Q1456480-2-BLK---L
Q1456480-2-BLK---M
Q1456480-2-BLK---XL
Q1456470-7-MRNR---L
Q1456470-7-MRNR---M
Q1456470-7-MRNR---S
Q1456470-7-MRNR---XL
I want to take off the end the ---L, ---M, etc but don't know if there is something that will do that like a right formula but not.
And then I want to isolate the color which is 3 or 4 characters before the --- Last I want the style number which varies in length and goes to the - before the color.
View 1 Replies
View Related
Dec 14, 2006
I have a data on a cell that says "Member: Name of Member"
The question is how could I remove the "Member:" and come up with "Name of Member"
View 9 Replies
View Related
Jun 9, 2007
This formula finds the last space from the right and returns the values less the last space and whatever is to the right-
=TRIM(IF(LEFT(Y25,2)="~C",REPLACE(LEFT(Y25,FIND("`",SUBSTITUTE(Y25," ","`",LEN(Y25)-LEN(SUBSTITUTE(Y25," ",""))))),1,FIND(" ",Y25),""),RIGHT(Y25,LEN(Y25)-FIND(" ",Y25))))
Results of a item of a whole size:
LAKAI KOSTON SLCT INDY SHOE BK
However- I forgot I had added a criteria to convert records that have a fractional value from decimal to a fractional value and an extra space shows up. I need to alter the above formula to handle this additional condition, returning the value w/ out the second space and the size 10 shown below.
Results of an item w/ a fractional size, ie. 10 1/2:
LAKAI KOSTON SLCT INDY SHOE BK 10
Linked to this post: new post started to help avoid confusion: but wanted to show prior material...
http://www.mrexcel.com/board2/viewto...766e4507d1a402
View 9 Replies
View Related
Jan 23, 2006
I am trying to compare multiple column in a worksheet to find
common component in all the columns and what is unique to a particular
column only. And list the results/finding in adj column. What i am
trying to accomplish is something as below.
BEFORE
Sheet1 Sheet2 Sheet3
Column2 Column2 Column2
02-1234-12 07-1234-12 02-1234-12
04-1234-12 03-1234-12 02-1234-12
05-1234-12 02-1234-12 06-1234-34
AFTER
Common to all Unique to sheet1 Unique to Sheet2
02-1234-12 05-1234-12 07-1234-12
View 14 Replies
View Related
Oct 12, 2011
Is it possible to take the information below and make one list out of columns F, H and J based on the common information in column C? For example, group all the 18s in a list, followed by the 44s.this is excel 2007
column CColumn Fcolumn Hcolumn J
Option code
18180L12369301/123/54
18180L12369301/123/54
18180L12369301/123/54[code].....
View 2 Replies
View Related
Jan 25, 2012
I have 12000 rows in cell a1 with more than 30 characters in them. I would like a formula to go up to 30 characters from the left, then go back to the first empty space, put that info in B2, the rest I would like to put into c2.
A1
This is a test to show what who to do this.
So I need
B2
This is a test to show what
C2
who to do this.
View 4 Replies
View Related
Apr 15, 2007
I would like to limit a text cell to 40 characters. If greater than 40 characters are entered, the rest is truncated. Can this be done without VBA?
I have limited it by going Data - Validation
This has done two things. 1. Gives an error box with Error Alert Turned on. 2. Allows greater than 40 characters to be entered if Error alert is turned off.
View 7 Replies
View Related
Oct 6, 2013
I have two columns of data and what I would like to do is have excel look over one column ( I5-I379) and for each cell that has say 30 in it I want it to return the value from a corresponding column (G5-G379), to form a separate column.......is this possible?
View 9 Replies
View Related
Oct 15, 2013
I have a formula that takes various data from a worksheet and places it all into a single row (N2:AY2) using the “=” formula. I then copy this and paste values, then I copy it again to it can be pasted into another application. There is also some various hiding/unhiding that goes on.
Question is, when I paste values it will truncate some numbers. For example if I have $70, it will change it to $7. 11/10 turns into 11/1.
Columns("M:AZ").Select
Range("M2").Activate
Selection.EntireColumn.Hidden = False
Range("N2:AY2").Select
[Code] ...........
View 2 Replies
View Related
Oct 21, 2013
How do I truncate a range of numbers where a number will not show more than 2 past the decimal ....and this be in vba as part of a macro.
View 3 Replies
View Related
Apr 13, 2009
I'm have a simple spread sheet that includes days and dates. I have a countif formula counting the individual days (as in: Monday = 5, Tuesdays = 2, etc...) I want a formula that returns the most frequent day in text format.
View 6 Replies
View Related
Mar 26, 2014
I have a column "ID" which contains ID . In the next column I want to get the name of the folder which contains a word file with same name as ID.
View 2 Replies
View Related
May 14, 2014
I have alot of project folders on my harddrive.
All in format: I:/12345-costumer-projectname/
The five digits are unique for each project.
I make calculations for these projects using an excel file. In this excel I also type the projectnumber (cell J2)
Now i would like to make a button. When pressed, it checks the projectnumber cell J2, looksup the corresponding folder and saves the excelfile in PDF format in this folder.
I have found macro to find files in folders, but none which do the above.
View 4 Replies
View Related
Mar 15, 2013
I need a macro in a workbook to look at all the files in the same folder that have "*att*.xls" in the name and determine and copy from the range A15:W515 only the rows that have data in at least columns A, C and D. Each file will vary as to how many rows there will be and there are more than the files with "*att*.xls" in the folder. The data will be on the only worksheet in each file and the worksheet is named "G2WAttendee_xls" the data from all the files need to be copied to the file called "Consolidated webinar reports.xls" (I am using Excel 2003) and to a sheet called "Attendance Data" and added to the end of the last paste.
At the start of the macro the current file "Consolidated webinar report.xls" should be saved to a sub folder of the current directory and have the date saved added to the name. The sub folder is called "Completed reports". The data in the original file on worksheet "Attendance Data" should be deleted.
At the end of the process all the files that have had data copied from them should be moved to the sub folder "Attendance reports consolidated" (This could be done as each file is closed if that is easier).
I have headings in row 1 of the "Attendance Data" worksheet that match the headings in the various files in the folder (which will always be in row 14 of the individual "*att*.xls" files).
The folder with all the files and the "Consolidated webinar report.xls" file is at path "Z:P and S MEvaluationsWebinar series 2012-13TB".
View 9 Replies
View Related
