I have data like this:
James English
Bea English
Bea Math
Pat English
Sarah Math
Sarah Geography
Sarah Art
What I want is
James English
Bea English Math
Pat English
Sarah Math Geography Art
I have ~350 rows of subjects. Is there an efficient way of doing this? At the moment I'm cutting and pasting.
I have a list of vehicle number plates (or License plates for some of you) that have been recorded on a video camera and then converted to a text file using recognition software.
Unfortunately the records are far from 100% accurate so any data manipulation (travel times between points etc) becomes a bit dodgy and clumbsy to allow for this. I want to keep all of the data entries but need to identify each vehicle with a little more accuracy. I'm not worried about whether the plate number recorded is correct, only that it is the same for each instance that a particular vehicle is recorded.
The data comes in looking roughly like this:
ABC123
DEF456
AC13
A123
DE56
DEF56
I would like it to look like this:
ABC123
DEF456
ABC123
ABC123
DEF456
DEF456
Or even this:
AB123
DF45
AB123
AB123
DF45
DF45
It doesn't matter which of the above or any other compination as long as there are at least 4 characters.
The surveys are taken over up to a week so there are generally about 5000 records for each time slot that need to be processed.
I have tried to modify the Fuzzy matching functions with no success.
I have two columns D and E that contain dates and values. Column D has the dates and Column E contains the corresponding values.
I need a macro to take all of the dates in column D (it is pulled from a different macro so the length of the two columns varies every time but starts at D2) and paste the first value in W1, the second value in AE1 , and so on (every eight cells).
I need to do the exact same thing with Column E except start at Y1, then AG1, etc (still every eight cells).
I will try to post an example later.
I have a list of products and would like to find what are the missing products which aren't ordered yet from the customer.
As the example: let's assume numbers from 1 to 9.
And I have some numbers, like this: 2, 4, 6, 7. What is the formula which can copy automatically the missing one into one cell?
See the attachment : howtodothis.xls
I know, how to copy multiple cell into one cell (like A4&", "&", "A5 etc), but how to find the missing numbers?
I have a workbook containing about 20 sheets. I need to find a way of pasting all the numbers in the A column in each sheet (sheets 1-20) to the last sheet so I get a great big list of all the numbers in the 20 sheets. How would I go about doing this?
Right now I'm cutting and pasting from sheet 1 to my last sheet, then from sheet 2 to my last sheet, then from sheet 3 to my last sheet... Is there a quicker way? The ranges in each of the sheets start at A2 and go down a few cells. Sheet 4 might have 4 numbers in the A column, sheet 12 might have 47, sheet 17 might have 8 and so on.
How would I go about getting all the A columns in the sheets to the last sheet?
I'll throw up a few screenshots if the problem is unclear
I have an master excel file with 20 sheets with names x,y,z,a,b,c,f,.... Each and every sheet has data which start from Row 7 and Column 2. Now i need to consolidate this data in one sheet in another excel file.
Consolidation should be like
Suppose X sheet has 20 rows and 4 columns of data which starts from Row 7 and Column 2, this data has to be copied and pasted in my new excel file copied on my desktop. Now first 20 rows are occupied in new excel file.
Now code should move on to master excel file Sheet Y which has 45 rows and 4 columns of data which starts from Row 7 and Column 2,this data has to be copied and pasted in my new excel file from row 21, which means Master excel file sheets has to be clubbed to one consolidated excel file.
In All the sheets in Master file Data starts from Row 7 and column 2.
Data range varies row wise in each sheet but column length is fixed to 4.
Each row in my file describes a country and a year. I have data for some years; I would need a way to copy these observations to the following years until there is a new value (conditional to being same country). On the other hand, if there was no value for that country, I would need a ".".
View 4 Replies View RelatedI am having trouble copying conditional formatting down my spreadsheet. When i copy it the number dont change. the formula i have in the conditional formatting is =O3
View 9 Replies View RelatedCell (A1) is formatted differently from the other cells of the same column. I added to it a conditional formatting (based on a formula) which I want to copy [alone] to the rest of the cells, without getting the other different formatting of cell (A1) copyed as well.
View 9 Replies View RelatedYou assisted me in writing a macro to move rows from one worksheet to another based on column criteria. I need a slight improvement to the code, ie it should only delete the rows after moving them if the rows are those that matched the criteria and not those that simply had blank cells for criteria after the rows moved are deleted.
Private Sub CommandButton1_Click()
Dim c As Range
Dim count As Integer
count = 0
If MsgBox("Are you sure you want to move rows to the archive ?", vbOKCancel, _
"Archive") = vbCancel Then Exit Sub
On Error Goto catcher
For Each c In Range("J1:J" & [sheet1].[a65536].End(xlUp).Row)
If c = "yes" Or c = "good" Then
count = count + 1......................
This has been kicking my can all morning! Should be simple. I'm trying to conditionally format an array from the value of a single cell.
=IF(A1<80, A3:A24,RED,0)
=IF(A1>80,A3:A24,GREEN,0)
=IF(A1>120,A3:A24,BLUE,0)
I have a spreadsheet which uses conditional formatting to highlight cells with certain values. It's works perfectly, except for a single cell. The formatting formula is and should be exactly the same as other cells in the sheet. For whatever reason, this cell will not change with the formatting rules. The cell is not protected, it doesn't have any oddball values or anything that I can see.
View 3 Replies View RelatedI have a large spreadsheet in which I have reached the limit for conditional formatting. (i.e. 2050 rows)
To resolve this I can add a single expression for conditional formatting across all cells. This means I need to create a more complex expression.
I effectively have two types of conditional formatting that are very similar. I have the standard Yes, No & A/L which I wand to shade accordingly. I then have alternating columns of Yes , No , A/L . I have introduced spaces to stop these values from being counted by another formula but still display yes and no. I want the shading to be the same in both cases.
I have built the following expression which does not seem to work.
=ISNUMBER(SEARCH("Yes",(ADDRESS(ROW(),COLUMN()))))
The two components of the expression work separately:
=ISNUMBER(SEARCH("Yes",P3))
=ADDRESS(ROW()-1,COLUMN())
Need to write a code that gives a whole (from column B to column S) row a bold upper-boundary if that row has a value in column B?
View 2 Replies View RelatedI have a set of values on Sheet1. These 'original' values will never change.
I then have a set of values on Sheet2. The 'updated' values get updated every month.
On Sheet3 I want to show the updated values again, pretty much a copy of Sheet2, but where a value is higher than that on Sheet1 or lower than a value on Sheet1 I want to use conditional formatting to show this.
Is there a simple way to use conditional formatting in such a way? I have a long list of values so don't want to individually create a rule for each cell.
I have one excel 2007 workbook with three sheets in it.
Sheet 1 is a master sheet with 1858 rows and 45 columns of data.
Sheet 2 has the qualifier data, that is, one column with 255 unique 6-digit integers each in its own cell.
Sheet 3 is a result or output sheet that is currently empty.
Here is what needs to be done.
Compare all of the 255 unique 6-digit integers from Sheet 2 with the integers in column number 15 (or Column O) of Sheet 1.
If there is a match then copy that entire row from Sheet 1 into Sheet 3.
I have a series of columns (L - X) each representing a diagnosed disorder (Dx), coded as binary, with 1=Positive Diagnosis (Success). The reference cell contains diagnostic codes; for each respective Dx column I need a 1 or 0, obviously. Each Dx category has a range of values (Dx codes), so I need to write syntax that reflects this range. For example, =IF(A1=>141,AND(A1=<239.99)),"1","0")
In other words, if cell value is 141 through 239.99 then return 1, else 0
Would it be something like this:
=IF(AND(A1=>141,OR(A1=<239.99)),"1","0")
i want to copy data from one excel workbook to other (or ever between diff sheet wud do ) on basis of search on a complete column by picking data one by one from other column & on finding the exact match copy 4 columns form one workbook(or sheet) to other. this has to be done 4 a bulk of data (1000+) so manually is next to impossible.
View 2 Replies View RelatedI'm trying to find a single conditional formula which summarizes multiple text occurrences in a range of cells. I managed to summarize the occurrences in a single cell, but my challenge is that I'm looking for a single (array?) formula for this.
I have a worksheet with column A which contains the condition and column B which contains a text field. I'm looking for a single formula to summarize all the 'AB' occurrences in the cells B4..B11 where the column A is Apple. I managed to do this by using an additional column (column C), however as my worksheet contains over 10000 rows, I would like to use a single conditional (array?) formula summarize the AB occurrences (multiple ' AB's can occur in a single cell) in stead of having to use an additional field per row.
I have attached a sheet as an example. I'm looking for a single array formula in cell C11 which summarizes the 'AB' occurrences in cells B4..B11 where column A is Apple.
I am looking for a method to conditionally format a variable number of cells below a single cell into which data is entered.
For example ABC = 6, DEF = 12. Therefore, if I enter ABC into D1 then D1:D6 should be conditionally formatted.
I have :
A1 = "Banana"
A2:10 = blank cell
B1 = xxxxxxx (always 7 digits) (number)
B2:B10 = xxxxxxx
How to fill the range of A2:A10 with "Banana" only if the value in Column B is having a value (or not blank)?
The second one is, how to delete entire row if the value in column B is text?
I have applied conditional formatting to a cell using formulas and i want to copy that formatting to a new cell using similar formula but pointing to different cells. Is it possible to do so? I have tried to copy and paste special>formats, but that just copies the conditional formatting with the same formulas.
Sample: =IF($C$5/$C$295>$R$5,TRUE,FALSE)
I want to copy to $D$5/$D$295>$R$5,TRUE,FALSE) without having to paste the new formula in each time.
How to copy and paste conditional formatting with our changing the cell values. My first row of data starts in row 4 and here is what I have in D4
If D4 is less than or equal to AA4, AW4, BS4, CO4 then it will highlight D4
Now I want to copy that format and paste it for all rows in column D. The problem I'm having though is when I paste the formula it doesn't change to match the row I'm on. For example, when I paste it in D5 this is what I get
If D5 is less than or equal to AA4, AW4, BS4, CO4 then it will highlight D5
How do I paste it so that it will update to show AA5, AW5, BS5, and CO5. I want to be able to do this for 500 rows.
I have a workbook containing two worksheets of staff training records.
The first work sheet contains a list of names and the dates they completed various training courses. I have used some simple date based formulas and conditional formatting to colour-code their name depending on whether their earliest retraining due date has passed, is coming up in the next few weeks, or is a long way off. The data is set out alphabetically, one person per row of data.
The second sheet contains the same list of names, but each person's data is split across two consecutive rows. The cells in column A which contain the staff names are merged in pairs so that the name heads both rows of data.
I want the colour coding of the merged name cells in sheet 2 to automatically copy the colour coding applied to the single name cell in sheet 1, but don't know how.
I'm using excel 2010.
I'm a 2007 user. I am trying to conditional format a range of F1:J10, where F1 will be conditionally formatted relationally to A1. G1 is relational to B1. H2 relational to C2, etc., all the way down to J10 relational to E10. My conditional format formula in cell F1 is >A1*2 (will format F1 as orange). I want to be able to copy the conditional formatting over so the formula in J10 for example would be: >E10*2 (will format it orange).
I omit the "$" in the conditional format formula in an attempt to prevent it from being absolute. But every time I copy the conditional formatting over to the other cells, the formula within those cells remains identical to the formula in the original cell.
123569LOC23 is in one cell and I am trying to copy the "LOC23" to a new cell. However, the amount of digits vary: LOC23, LOC1,LOC15 etc all the way down the column.
Excel 2007.
I have conditional formatting set up so that the cell becomes highlighted if it contains a specific text.
Example of wanted conditional formatting:
Conditional formatting rules manager
Rule
Format
Applies to
Stop if true
[Code] ......
For some reason this formatting is inherited by another adjacent cell as we continue to input information.
For example:
Column M is formatted so if 'AP' is placed in any cell in that column the cell fills pink. As more information is inputted into the sheet, the conditional formatting copies to Column N. It does not happen with every entry and I have not been able to isolate the specific steps to recreate the copying. Multiple people use the same sheet and fill it out and needed.
After working with the sheet changes are made to the "applies to" column without people meaning to.
For example:
Conditional formatting rules manager
Rule
Format
Applies to
[Code] .......
I would like to make this formatting so that is only applies to the Column M and not "travel" to other cells of the sheet.
I am working on a project to import cellular phone usage data from Sprint into a workbook.
All cells in Column A are blank. Column B contains 1 instance of the cell phone account #, followed by dates of calls made, which continues until the next cell phone account # appears, and its calls made. The # of calls made obviously varies per account #.
The dates are in date format. The cell phone account # is in text format and is formatted with blue fill.
I need to copy the cell phone account # into column A for each line that has a date value in ColumnB, but the value copied must change when it reaches the next cell phone account #, so that the calls are matched up with the cell phone account # and can be imported into SQL.
Acct #
Date
Time
City Called from
State Called From
Called #
City Called
State Called................
Find Multiple instances of Numeric Criterion in Row & Return To a Single
Column.
I have a Dynamic Named Range "Data" spanning 10 Columns and many Rows.
Each Row may contain duplicates of the Numeric Criterion.
I would like to find ALL instances of a specific Numeric Criterion across
each single Row in the Dynamic Range "Data" and have the Results returned to
a New Sheet in a single column.
NEW Sheet:
The Numeric Criterion is housed in G5.
The matched criterion should be returned to the New Sheet starting at G7.
Duplicate instances in the same Row should ALL be returned to the same cell
in Column G on the New Sheet.
Sample Data Layout:
Columns I J K L M N O P Q R
Row No.76 1 0 1 1 0 1 1 1 0 1
Row No.77 2 2 3 2 1 2 2 0 0 0
Row No.78 3 3 3 3 3 0 3 0 3 0
Scenario:
Looking for Numeric Criterion 1 (one).
Expected Results - New Sheet:
Row No.7 Column G (Cell G7) 1111111
Row No.8 Column G (Cell G8) 1
In Row 76 of the Sample Data ALL seven Numeric Criterion of 1 (one) should be
returned to the same cell G7.
In Row 77 of the Sample Data there is only one Numeric Criterion of 1 and it
should be returned to cell G8.
I want to copy the current region on sheet 3 and paste that into sheet2 starting with cell E4. But I don't want to actually use the copy method. I believe there is a way to do this. I was thinking the following:
[Code] ......
The idea I had was to simply state that cell E4 would be assigned the value of CurrentRegion on sheet 3. Is there a way to bypass the copy method?
If not, how to copy, then paste?
