I have excel data containing in column A "Plot number" which is repeated and other four column is land area and the next column is "Land Owner Name". Now I want to merge same plot no if the land area is same if differ then I want to leave as it is. And another thing I want to do that the all land owner name is merge in a row.
View 8 RepliesIn colmn A:A i need returned, which numbers (if any) are repeated and how often each repeated number occured.
Admitedly... I'm lost on this one.
I am trying a macro to merge cell without losing the data until I find a next cell with the data - This merging should be pertain only for the column selected. Is it possible to write a macro and preform this activity.
View 1 Replies View RelatedI need a macro, that will merge the cells and the data in cell A1 with data in B1, and the data in cell B1 will paste to the end of data in cell A1.
Column A Column B
KL8CB6S90DC505233
3GYT4MEF9DG331323
I need the final result to look like this:
Column A
KL8CB6S90DC505233
3GYT4MEF9DG331323
I need it to complete this for all data in Column A and Column B.
Normally i have a row and data as below:
Item
Red
Comfortable
New
Car
1
and i would like to merge them into one cell like this(with spacing between them):
Item Red Comfortable New Car 1
Can VBA do this move?
Is it possible to associate a range of cells containing different information based on like values in other cells?
Example:
How it currently appears in spreadsheet
Name Acct#
John Q. Public 12345
John Q. Public 23456
John Q. Public 34567
John Q. Public 45678
Jane Example 11111
Jane Example 22222
Wanted Result
John Q. Public 12345 23456 34567 45678
John Q. Public 12345 23456 34567 45678
John Q. Public 12345 23456 34567 45678
John Q. Public 12345 23456 34567 45678
Jane Example 11111 22222
Jane Example 11111 22222
I know how to remove the duplicates afterwords to get one unique record.
I have a spreadsheet (>10,000 rows) with data referring to a grid cell map. Each grid cell on the map has an ID and associated data. About 10% of the grid cells have duplicate records, but with different data. I need to produce a spreadsheet with a single record for each grid cell that contains the data from each of the duplicate grid cells. Some occurrences may be >2. Example below.
Current
[TABLE]Grid#;Data
1;a
1;c
2;b
3;a
4;b
4;c
5;d
5;d
5;f
[/TABLE]
Needed
[TABLE]
Grid#;Data1;Data2;Data3
1;a;c
2;b
3;a
4;b;c
5;d;d;f
[/TABLE]
In the sample file attached, I want to do a lookup with the employee id in the master sheet and find the multiple results in the "open_text" sheet. From there, I want to display the open text results in one row per employee on the master sheet. If there are multiple comments, then the results will be displayed in separate cells to the right of each other (filling comment1 through comment5 if needed).
My main goal at the end of this is to be able to do a mail merge out of the master file. This is why I want the results in one row per employee.
Suppose I have the following data in column A from A1 to A12
Pass
Pass
Pass
Pass
Exceed
Fail
Fail
Exceed
Pass
Pass
Fail
Exceed
I want two formulas one to count the how many times the following pattern of data have been repeated in the range:
1- Exceed after Pass
2- Exceed before pass
I have simplified this spreadsheet from the original to get to the core of what I want it to do. There are three tabs: 1) tab:"query fields" is an area that places data that is retrieved from the web, in this case it retrieves tables from yahoo finance given a ticker input. The ticker input cell is currently on the "score card" tab. 2) tab:"score card" in this tab you can input a ticker symbol in the indicated box and it will populate the "query fields" with corresponding data. This tab also extracts certain fields from the retrieved tables and performs a pass/fail test then produces a score %
This is great if I'm doing one symbol at a time, however I eventually would like to 'loop' this process such that a score field is populated for a list of tickers, hence tab 3) "list". Ideally I'd create a control button to initiate the web retrieval process and it would go down my list, retrieve the table data, perform the pass/fail tests, then populate the corresponding score % in the cell next to the ticker symbols. I know I can keep running separate web queries for specific symbols, but I guess my secondary goal is to have this be as dynamic as possible, since the ticker list will change and the list could be sizable at times.
CSH- web query.xlsx
I have a list that I want to show in a drop down box. The list is like this;
MPK4
MPK4
MPK4
MPK4
MPK4
SPC600
SPC600
SPC600
SPC600
SPC600
SPC600
SPC600
WK-7
WK-7
WK-7
WK-7
WK-7
WK-7
Can a drop down box be made to only show each item 1 time?
Rows 1 to 12 of the attached .jpg file shows the format that I receive from a University. However, in order to check enrolments, etc., I would prefer it to be in the format as shown on rows 15 to 19, that is, one complete student record per row.
As you can see, not all students are studying the same number of subjects and the subject mix is also different.
I am finding it very difficult to extract the Project reference data for each employee, I have attached sample working file for reference.
For Example
I have a project names in Rows and Employee names in Columns and each employee fill the working efforts in multiple projects and i would like to extract which all project they have worked for each month. Since the data is very huge i am not able to progress fast in collecting the data
Project NameEmp1Emp2Emp3
A
B 1015
C
D 2012010
Working.xlsx‎
Margo
12/12/86
188478007
7/19/12
801
Anthem BC/BS
Margo
12/12/86
188478007
7/31/12
806
Anthem BC/BS
[Code] .......
I have this info on my excel spreadsheet plus 500 more lines. What I am looking for is if a name repeats then the info in column 2, 3, 5 and 6 for that name needs to be brought to new row. Example would be if I used the name karen on the next row then I would like her info from the desired cells to auto populate so I don't have to keep entering them and so on with new and old names. I have been trying to get this done for months know and have had no luck. My sheet already has over 500 lines of names and it would be nice if I could use the data from the past as well as the future to populate these cells.
I am trying to write some code that will look in an excel cell find if it contains the word that I am looking for and make it bold. I have written the following code so far
VB:
With Worksheets("Label Print").Cells(i, J)
.Characters(Start:=InStr(.Value, "Name"), Length:=Len("Name")).Font.Bold = True
End With
[COLOR=#3E3E3E][B][/B][/COLOR]
The issue is that if "Name" occurs twice (or more) in a cell it will highlight only the first.
I want to count the nr of words in cell without counting the repeated words.
For example, in "this is a ball and that is a square", the total nr of words is 9, and the total nr of non-repeated words is 7, because "is" and "a" are repeated twice, and I just want to count it once.
Per the table below, I compute how many months to average project costs over, establish month in which the costs begin, and these accumulate in the work in progress (WIP) end columns, however in the month the final stage (to hit 100%) of costs are complete, the project is invoiced and the WIP is released - Noting the month in which the last costs are incurred are not added to total to reverse out. The 3 projects and final columns demonstrate the different scenarios and the results I would expect to see, I just can't formulate anything to release the previous months WIP without it repeating the negative for the rest of the year - if that makes sense?
Sheet1 *ABCDEFGHIJKLMNOPQRSTUVW1
*********Project start point*Forecast costs incurred*Work in progress2**% project complete
****** * * * * * * 1 * * * * * 2 * * * * * * * 3 * * * * * * *4 **********3
ProjectJun-14Jul-14Aug-14Sep-14CostsStagesMonths*Jun-14Jul-14Aug-14Sep-14
[Code] .......
How to remove words that are repeated and adjacent within a cell? I have about 20,000 cells to do this with so the spellcheck is not an option.
View 9 Replies View RelatedI have a excel doc with 8000 names I need to delete the people who are listed on 7 rows.
There are no blank rows and Colume A has the names sorted.
is there a formula which can locate instances of same text even though due to spaces it may look different? for example, "bad apple" and "badapple" are essentially the same.
View 2 Replies View RelatedI have three source workbooks that needed to be consolidated into a master workbook pasting the data with values & formatting.
The Master workbook also contains 6 additional worksheets that link to the data pulled in from the source files.
My question is how do I write the code so the source files populate the master in a specific order.
For example, the 3 source files are named "Central" "NED" and "WEST" and I need them to populate the master workbook in that order.
This is the code I am using to consolidate the data:
Option Explicit
Sub ConsolidateSheetsFromWorkbooks()
'Author: Jennifer Starr
'Date: 7/12/2013
'Summary: Open all files in a folder and merge data (stacked) on all
[Code] .....
removing duplicate rows and move other data frm rows to columns.xlsx.
I am attaching a sample excel sheet showing what I need to do.In the first tab, I have a list that includes duplicate rows (first column only). I want to remove those duplicate rows but I don't want to lose the data in the following columns which can be unique or duplicates as well.
see the desired result tab in the sheet to get an idea of what I am looking for as the end result.
Keep in mind that the actual source file I am working with could have up to 50000 row, and the expected results could be around 2000 rows. So nothing can be done manually.
I got the following data and I like the results to be:
Data
Company | Country
ABC | USA
ABC | London
BBB | USA
CCC | USA
Results:
Company | Country
ABC | USA, London
BBB | USA
CCC | USA
Is it possible to this under Pivot? as I need to consolidate lots of data..
I have a large spreadsheet where some data are similar. for Example
Robert $1000
Louis $600
Glen $350
Doreen $2300
Robert $500
Louis $600
Glen $750
Doreen $300
Robert $3000
I want to merge all Robert together, all Louis together and show as one Robert $4500. How can I do that ? Should I use a Look up.
I have 2 data sets which i want to add together. Here is an example.
Data sheet 1
A B C
SAC
L
1
INPUT
L
4
BLIN
L
12
[Code] .......
What i want to do without inserting every line manually.
A B C
SAC
L
1
FIX
L
2
[Code] ........
I have an empoyees first name in A1 (Joe) and Surname in B1 (Bloggs), however, I want "Bloggs, Joe" in C1?
View 2 Replies View RelatedI'm quite proficient in Excel but am struggling here, would really appreciate any help
I basically have 80 tabs with IP addresses in Column A and then information in Columns B, C and D related to this.
What I want to do is bring all this together in one sheet
Possible?
Right now I need help mergng 2 files:
#1 One is a txt file with emails
#2 the other is a .csv excel file which contains several fields: email, name, address, etc
Basically what I need to do is to create another .csv excel file that contains all the emails in file#1 that are also present in the file #2 plus their corresponding additional fields ( name, address ) etc
I extract data from a data base and return the query in details, i need to merge 2 rows of data that has the same project number. example i have project ABC with billable time of 10hrs @rate of £200 and unbill time of 6hrs @rate of £0. The query will split the data as there is a difference in the rate.
I need a formula that recognise that it is the same project and merge the row together!
Need to merge the data on two wrk sheets.data is as given below data in wrk sheet 1:
site_keybrandsiteQ count
BU04191BU4191332
DI00768DI768512
DI01616DI1616480
data in wrk sheet 2:
site_keybrandsiteNQ count
DI00551DI551171
DI00768DI76812
DI00982DI9822
RA07922RA79229
my final report should look like
site_keybrandsiteQ countNQ count
BU04191BU41913320
DI00768DI76851212
DI01616DI16164800
DI00551DI5510171
DI00982DI98202
RA07568RA75681900
RA07922RA792209