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.
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)?
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.
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
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
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 ...
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] .....
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:
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.
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
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.
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?
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
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?
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.
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
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.
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
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.
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.
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?
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.
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.
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.
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".
