Warn If Two Columns Share Same Data In Two Or More Rows
Feb 19, 2014
Right now I have a script that will highlight any rows where columns "Unit1" and "Unit2" share the same value.
The script is below:
[Code] .....
How to modify this so that it only highlights rows where the same data is duplicated twice?
For example if both columns had "The dog runs" as their value in the same row, then this would only get highlighted if two or more rows on the sheet also had "The dog runs" for both their value.
Example: example.jpg
View 7 Replies
ADVERTISEMENT
Feb 15, 2013
I am wondering how I can highlight rows that contain the same text across selected columns (not all). For example, consider the following table:
ID#6527
Jay
yellow
dog
[Code]...
I want to focus on Columns B, C, and D. I would like rows 3 and 5 to be highlighted, since they share the same text across the target columns. I assume this can be done via a formula in Conditional Formatting, but I'm not sure.
View 3 Replies
View Related
Jun 24, 2008
I want to combine the following two procedures, but I can’t figure out how . The purpose of the procedures is to warn the user to save data before importing and overwriting data that was already there.
Sub Is_sheet_empty()
If Range("your range") = vbNullString Then
Call Add_New_Data
Else: Call Sheet_Not_Empty
End If
End Sub
Sub Sheet_Not_Empty ()
Dim Response As VbMsgBoxResult
Response = MsgBox("Do you want to replace this data?", vbYesNo + vbQuestion)
If Response = vbYes Then
Call Add_New_Data
Else: Response = vbNo
‘Do Nothing
End If
End Sub
View 5 Replies
View Related
Feb 27, 2013
combining 2 rows that share 1 cell so that it is easier to format cells. For example: A4 and A5 have been combined with B4 and B5 to form 1 cell that has content. How can I convert this to a cell that only occupies A4?
View 1 Replies
View Related
Mar 26, 2008
I have a large worksheet that for some reason I can’t share.
-It does not have any lists, that I know of, do not know how to see if there are any
-It does have validation, but I have other workbooks with listed validation that are shareable
-It is not protected
-Does not have any named ranges
-Is not read only
-I have saved it as another file name and no luck there
-I have copied the one sheet I really need into a separate file to try that it did not work,
View 2 Replies
View Related
Apr 16, 2014
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.
View 5 Replies
View Related
Aug 5, 2009
a code, that looks at range "A" (xls down) and then goes over that amount of columns in "I" and warns if there are any blank cells and ends the macro if so.
View 13 Replies
View Related
Jun 10, 2013
I have a row on a tab that I always want to hide before I print or save. Is it possible to get a message to pop up if I try and print the page while row 33 is not hidden?
View 5 Replies
View Related
Jul 3, 2007
Is it possible to check, with VBA code running some sort of "If" function in the background in an open workbook within which the VBA code is placed, whether a user is opening (or attempting to open) the VBA Editor? This should see any attempt being made, whether the user uses Tools>Macro>Visual Basic Editor, Alt + F11 or right clicking on the Excel icon top left to "View Code".
View 2 Replies
View Related
Dec 18, 2013
Column A is numbered 1 -100 successively Column B thru D contains data that goes with the assigned number in column A. I need to be able to move rows of data in column B through D to a different set of rows all at the same time (not one cell at a time) without disturbing the set numbers in column A. And with that, have all the other rows of data automatically adjust accordingly(not to be deleted or replaced).
View 1 Replies
View Related
Feb 6, 2014
VBA Userform: Warn user if all checkboxes on a userform are unticked.
View 2 Replies
View Related
Mar 14, 2008
I am having difficulty getting a script to work that manages duplicates being added to a worksheet from a userform. Each record is assigned a unique ID when it is added to the worksheet initially. The problem that I am having is that the user wants to add non-unique information to the worksheet in unique records periodically. I can do that, but I am looking to make the application more user-friendly by reducing the number of msgboxes he has to respond to in order to do this. I have some code below which works, but it needs to be smarter.
Private Sub UpdateContact()
Dim strAnswer As String
'Copy values from Customer Form controls to Data array
tbxWrkTel.Text = Format(tbxWrkTel, "000 000 0000")
tbxMobile.Text = Format(tbxMobile, "000 000 0000")
tbxHomeTel.Text = Format(tbxHomeTel, "000 000 0000")
If Not WorksheetFunction.CountIf(Sheet1.Columns(1), tbxCompany) > 0 Or _
Not WorksheetFunction.CountIf(Sheet1.Columns(2), tbxContact) > 0 Then 'To avoid duplicate data
If tbxCompany = "" Then tbxCompany = "-".........................
View 6 Replies
View Related
Oct 24, 2012
Using the following code to remove empty rows based on whether a specific range of columns is empty. The code works if the cell has a zero, but not when the cell is blank. An example of the data is attached.
VB:
Public Sub DelRows2()
Dim Cel As Range, searchStr, FirstCell As String
Dim searchRange As Range, DeleteRange As Range
[Code].....
View 1 Replies
View Related
Feb 12, 2009
When I was using Excel 2000, there was an Excel add-in where I could highlight rows of information and then transpose these into columns of information. Since we have upgraded to Excel 2003, the same Excel add-in does not work and I have not been able to find a simple solution to transpose my information from a horizontal view to a vertical view or the reverse.
Or do I just have to move 53 columns (weeks) of 4 rows one cell at a time to 4 columns of 53 rows (weeks)?
View 3 Replies
View Related
Jul 9, 2014
I have a set of task descriptions that I am attempting to trend on. Some of these (Column B) have the customer's name added to the description; others do not. I need to be able to make a list of task names (ColumnA), removing the name from the text string.
The formula I am using is [=LEFT(B3,FIND("for",B3)-2)].
The problem I am having is when the description does not contain the "for" built into the formula, I get "VALUE" error. What adjustment can I make to the formula to pull over the Description if the "for" does not appear in the text string?
View 3 Replies
View Related
Nov 16, 2009
I am trying to sort out some item numbers with their page numbers.
My file is in the following format:
View 6 Replies
View Related
Apr 28, 2009
The title makes it sound simpler than it is. I have 2 columns of data which I need to transpose.
The difficult bit is that Reference Number 1 may have 3 reasons (so therefore 3 rows) that need transposing into 4 columns (reference number, reason1, reason2, reason3)
Is there a way to do this?
I have attached a spreadsheet with the original table and how I need the end result to look.
View 14 Replies
View Related
May 18, 2014
See attached template I am working on. I want to calculate the number of hours worked over specific dates e.g. between 19-25 May 2014 and 26-31 May 2014 for employee "Bird, Ken" - the result is to be placed in cell D19. Tried sumproduct but without success.
celeste template.xlsx‎
View 2 Replies
View Related
Aug 5, 2008
i have a problem in copying many columns to to rows that originally contains data, this is gonna be really complicated so here is wt i want, let's assume i have this table .....
View 6 Replies
View Related
Nov 19, 2008
I am trying to clean up some data, which is organized horizantally AND in rows, as per the attached screenshot.
The fact that there is data horizontally across seven week days and in rows for the weeks of the year makes it impossible for me to use the simple TRANSPOSE feature, of which I am aware...
I have quite a couple of worksheets I need to apply this operation on, so manual work is the worst solution...
View 13 Replies
View Related
Mar 26, 2009
I have an excel spreadsheet which contains data for customers and the last date they were seen at an appointment (along with various other bits of info).
The way the data is exported from my database package means that each customer has one row per appointment, i.e. row 1 contains john smith, 01/01/2009, row 2 contains john smith, 03/03/2009, row 3 contains john smith 01/04/2009, row 4 contains joe bloggs 12/02/2009, row 5 contains joe bloggs 27/03/2009. Some customers may have 4 or 5 appointments listed whereas another customer may only have one. I need to get the appointments all onto one row per customer so that I can calculate the number of days between appointments. I have tried to use transpose, but with 8000 rows it takes forever.
View 4 Replies
View Related
Mar 5, 2013
I am trying to convert some data...
I have a spreadsheet of 1000 rows on my spreadsheet and I am trying to convert them to 2 columns. Below is an example
Antidiarrheal AgentsBrand or SeriesApplicationGeneric Drug NameStrengthTypeDosage FormContainer TypeVolume
Antidyskinetics Brand or SeriesApplicationGeneric Drug NameStrengthTypeDosage FormContainer TypeVolume
I want it to look like this:
Antidiarrheal Agents Brand or Series
Antidiarrheal Agents Application
Antidiarrheal Agents Generic Drug Name
Antidiarrheal Agents Strength
Antidiarrheal Agents Type
Antidiarrheal Agents Dosage Form
Antidiarrheal Agents Container Type
Antidiarrheal Agents Volume
Antidyskinetics Brand or Series
Antidyskinetics Application
Antidyskinetics Generic Drug Name
Antidyskinetics Strength
Antidyskinetics Type
Antidyskinetics Dosage Form
Antidyskinetics Container Type
Antidyskinetics Volume
Is there a formula or a suggested way of doing a macro to acheive this?
View 1 Replies
View Related
Jul 8, 2013
I have a piece of equipment that I use for temperature profiling temperature controlled chambers. The equipment has up to 16 channels for logging data, although we typically only use 9. The data output from the equipment is by Text file and we convert this to Excel for manipulation into a format that can be issued as a certificate.
The test file outputs the data mainly in a single column and similarly when it is converted to excel the data remains in a single column. To enable us to present the data in a format that is presentable as a certificate we most transpose the data from each channel from a single column into an individual columns for each channel. Because I have limited knowledge of Excel, this operation is very time consuming and I'm sure there must be a quicker way to perform this operation in Excel.
I have attached a copy of the text file and the resulting Excel file with the data in it's original format and also in the final format. This is only a snapshot of the data over 30 minutes and we normally carry out measurements over 24 hours.
View 7 Replies
View Related
Sep 24, 2009
I have the following spreadsheet with 4 columns of data:
1000001245aMr Fred Blogs
1000001270a1 Blog Street
1000001270bNorwich
1000001270cNorfolk
1000001270eNRx x99
1000001270gThe Manager
1000001270k01603 555 555
1000001270l01603 555 555
1000001270qManager
1000001270xN
1000001270yN
1000002245aMrs Fredilina blogs
1000002270a10 Blog Street
1000002270bNorwich
etc etc for about 36,000 rows.
What I need is for each unigue reference ID (column A),
I need all corresponding data moved up into a single row in serperate columns, something like this:
1000001 |245|a|Mr Fred Blogs| 270|a| 1 Blog Street|270|b| Norwich | 270|b|Norfolk |etc
1000002 |245 |a |Mrs Fredina Blogs |270|a|10 Blog Street |270|b|Norwich| etc
Each entry doesn't nessesarily have the same number of rows.
I would class myself as a beginner VBA bod, but the solution to this one is illuding me a present.
View 6 Replies
View Related
Oct 15, 2009
I'm using Excel 2007 and I need to extract values from rows from a data dump into columns. Here is sample data (this is dummy data, but the format is the same): ...
View 13 Replies
View Related
Dec 2, 2009
I have a spreadsheet that consists of columns of data and I need to covert it into rows depending on how the data is set up. In column A, there is a list of invoice numbers. Columns B, C, & D are as follows:
Column B: Sales Rep Name
Column C: Sales Rep Number
Column D: Sales Rep % Split
I want to have a single row for each unique invoice number. As an example, I have the following:
Row 1: invoice ABCD-1234 Sales Rep 1: 55%
Row 2: invoice ABCD-1234 Sales Rep 2: 25%
Row 3: invoice ABCD-1234 Sales Rep 3: 20%
I want the output as follows:
Row1: invoice ABCD-1234 Sales Rep 1: 55% - Sales Rep 2: 25% - Sales Rep 3: 20%
I have attached a sample workbook with two sheets; one containing the raw data and the second, which shows the desired output.
View 2 Replies
View Related
Dec 17, 2009
I really could use some help here. I have ~25,000 rows of data, contact information, and I am trying to convert it to columns with the corresponding 9 column headers (Full Name, Job Title, Company, Business Street, Busines City, State, Business Phone, Business Fax, Web Page, E-Mail Address). The challenge is that each record does NOT have the same number of rows...?? I am new to this forum, so please forgive me if this a stupid question. I've attached the data for three records below and also in the attached spreadsheet ...
View 14 Replies
View Related
Dec 3, 2012
I have a single sheet that has 6 fixed columns of data in but the number of rows can vary per user per import. Users can actually insert rows as required. The seventh column has my macro buttons present. It looks and works great for about 20 rows (one screen) full. If the number of rows gets to be more than that and the user needs to scroll to modify those lower rows the macro buttons roll off the top and are not visible to access. Is there a way to keep the macro buttons in the same location (column G) and only have the data (columns A-F) scroll when required by the user? This way the macro buttons are available on the rows beyond the first 20 or so for the user to use, instead of having to scroll back up to use them. I've tried using the split and freeze functions but neither worked.
View 5 Replies
View Related
Jan 15, 2013
I have an Excel sheet that is in rows. I want to separate the data into columns. Each sample is separated by a space in the rows. So sample 1 is A1 is the name and B1 is the actual name. A2 is the description and B2 is the description. Each sample is +or- rows. Sample 1 may be 8 rows but sample 2 may be 6 rows. What type of formula or macro do I use to arrange the samples into columns instead of rows so the data will be like A1 name, A2 description, etc... and A2 will be sample 2 name, and B2 will be sample 2 description, etc...
View 7 Replies
View Related
Jan 12, 2010
I am trying to figure out an efficient way to convert data into Excel. The data is not in a row/column format. The format that I received the data in is as follows (2 columns; column labels repeating for each name in column A, and the corresponding data in column B. See below. Any ideas on how to best to convert this data into column labels across the top and data in rows under each column heading?
Column A Column B
Name John Doe
Address 123 Maple Street
City Any City
State Any State
Zip 12345
First Name John
Last Name Doe
BLANK ROW
Name Jane Doe
Address 123 Maple Street
City Any City
State Any State
Zip 12345
First Name Jane
Last Name Doe
View 9 Replies
View Related
