i have got two tables each on a different sheet. The column headings are not the same but in both tables column A contain names. Users can enter names in column A in the first table, and these should be copied to the second table. However, users can insert rows in the first table which should automatically be copied to the second table to ensure the tables haven an identical number of rows (names)
View 2 RepliesI am trying to join 2 tables which have identical columns. The columns are:
A B C D E F
Date : Name : ID : Violation : Date Sent : Comments
Table 1 has information in columns A-E, Column F is blank
Table 2 has information in columns A-F.
I want to be able to check the information in Columns A-E in both tables and add the information in Column F from Table 2 to Table 1 where Columns A-E are an exact match.
I have several sheets with tables that need to contain one identical common column called "product name" while all the other columns are different on each table. The issue is that this list in the "product name" column changes by adding, removing and even name edits and currently I need to edit each every table for each change. How can I only have one uniform list to modify that is represented on all these tables that updates.
I have tried a master list with links but causes issues when a product name row is deleted in the master it does not delete the row in the others as well as adding a new name to the master list requires me to recreate a link in every table which defeats the purpose of it trying to save me time having to modify something different on every table. Also each of these lists are sorted or filtered often which I want to avoid being reflected across all of the other tables and lists.
I need to build a macro which copies 3 rows every day and pastes the row data into an identical sheet. The three rows will have column "D" as =today(). As the days progress the three rows will change accordingly ( tag to the today's date)
e.g. 28/1/2014
28/1/2014
28/1/2014
I need the macro to recognize the date when pressed and copy the corresponding rows of data and paste them into an identical sheet with the same date. The second sheet is an archive sheet. The date will tick over as per the calendar.
I want to delete some rows that are identical. Aside from creating a macros to do this, is there a way to find a row with a specific criteria and delete it if value returns TRUE?
View 1 Replies View RelatedIn each row of my data set, columns A thru E contain different three-digit numbers, in ascending order (e.g. 012 312 344 798 880). No number is repeated more than once in a single row, and no two rows contain the same set of five numbers. Column F contains a value (these are what I'd like to sum).
The variables I want to create a formula with are each three-digit numbers, call them X and Y. I want to find the pairs of rows where one row contains X in one of the first five columns, and the other row contains Y and the same other four values as were contained in the four non-X cells in the X-row (note that X and Y may or may not be in the same column, since each row is in ascending order). If X=344 and Y=955, an example of a match would be one row with 012 312 344 798 880 and another row with 012 312 798 880 955. For each X and Y combo there could be many such matching pairs of rows (with different sets of the other four values). I'd like to sum all the values in the X rows of such pairs (and then I'd do the same with a different formula for the Y rows).............
Is it possible to find duplicate rows that are not EXACT identical. Conditional formatting can find duplicate rows but they must be identical exactly(ofcourse)(even extra SPACE,COMMA,DOT wont find duplicates)
I have name and address(street address) columns. I don not want my invitation cards or mails go to same address twice.
As you can see in the file ..all 5 records belong to same address.(address entered in diff styles,some comma,space extra..etc) 4 and 5 are Son and Wife of 1st person. Row 5 and 6 have same text but one extra space in 5th row makes it unique so i need to eliminate such records with same address So can i set some formula so that duplicate rows will be highlighted if any 3-4 words match ?
Attached File : inwell.xlsx
I found a useful resource on the web that gives a macro that deletes rows when the cells within a SINGLE column are identical. [url]
Does anyone know of a macro that can do more than that, one that will delete rows if ALL cells within ALL columns are identical?
For example, the macro should delete row 4 of the attached spreadsheet. It should leave row #2 there because it's the orginal row. But the duplicate row #4 should be deleted. The macro should leave row #5 there because not all columns are identical for that row.
I have over 2000 rows with 20 columns and i need to find and remove identical rows. I can do a match with MATCH() by concatenating the columns and comparing them as a whole but i need to apply this to the whole table and i need it to remove all the duplicate rows it finds.
View 5 Replies View RelatedIf I have the following worksheet #1 ('|' = column separators)
A|3445|Mr Bloggs|20/10/2000
A|6777|Mr Cheese|20/11/1986
A|9467|Mr Mouse|08/12/1998
and another worksheet #2;
R|16432|3445|324|22|433|123
R|13445|3445|333|222|221|54
R|32287|6777|341|9889|2219|62
R|28775|9467|23|344|853|221
How would I copy each row in worksheet #2 to the corresponding row in worksheet #1, given that there are clear identifiers (3445, 6777 and 9467), there are two rows for Mr Bloggs in worksheet #2 and I would only want one in worksheet #1, and I can't just go through and copy-and-paste because there are 16,000 rows in each worksheet and I will have to do the same in two months' time? I would like to end up with one worksheet that has;
A|3445|Mr Bloggs|20/10/2000|16432|13445|324|22|433|123|333|222|221|54
A|6777|Mr Cheese|20/11/1986|32287|341|9889|2219|62
A||9467|Mr Mouse|08/12/1998|28775|9467|23|344|853|221
i have a database. column a is the last name. column b is the first name. column c is a person's title. i regularly have to merge this database with another from another department and compare to see that all staff are processed and that titles are correct. i have been trying to write a formula that can pull from the contents in any given row, search the rest of the sheet to find if there is an identical person listed and finally let me know if the title is the same. i just need a "true" or a "false" if both conditions are met.
View 6 Replies View RelatedI have an excel sheet that I'm trying to format. Basically, the excel book contains sheets (with the months), and each sheet contains a list of clients, their package, their status, date assigned, and date updated.
I've made another sheet (Sheet2) that fetches everything and consolidating it to one sheet. Sheet 2 also cleans up the data to display only the items that are 'completed'.
The issue I'm having now is in splitting the list (assuming it has been cleaned up to show only the 'completed' items. I would need to split the list first by the date they were updated, and then if they were assigned the same date or not. In short:
--Get all items with date updated = Month1
----If date assigned = Month 1: List items under table 1
----If date assigned < Month 1: List items under table 2
I've used macro on the excel (and I'm not sure it's efficient enough. I'm not proficient with VB, so...). I'm not sure if it will work on Mac.
Is it possible to add a row to the pivot table. All my fields run across the top of my excel sheet but i need a calculated field to show under the row under the total of each group. The values in this row must equal the value total in one of my fields. Is this possible ?
View 1 Replies View RelatedThis is my data:
This is the Pivot Table I created:
The Pivot Table is exactly what I want and I like that I can also sort by school or district. The problem: Cells B6 through E6 show up with correct information but the totals below that are all wrong. They seem to pull the same data from question 4.
I have two tables with "X" and <Blank> data. The tables share the same column headers. I want to compare the rows of each table and if the rows contain an "X" in the same column then it puts an "X" in the third table. I have attached a sample file with my expected results on the third worksheet. I have created named ranges if that helps the formula writing.
Name1 =Sheet1!$A$2:$A$5
Name2 =Sheet2!$A$2:$A$5
Produce =Sheet1!$B$1:$E$1
Group1 =Sheet1!$B$2:$E$5
Group2 =Sheet2!$B$2:$E$5
Sumproduct seems to do the trick if I turn the "X" into "1" and <blank> into "0". If there is a match the value is greater than "1".
=SUMPRODUCT(Sheet1!$B$2:$E$2,Sheet2!$B$2:$E$2)
I prefer to use "X" though if I can. I would like to automate the formula instead of manually adjusting the ranges for every cell.
I have a spreadsheet that contains two input tables: Parallel and Perpendicular. Next, the user can select a group which a certain entry belongs to. Now what I want is a "summary" of these entries in a table that has no blank rows and combines both Parallel and Perpendicular entries by the Group. Please note that there is no fixed number of inputs for any one group i-e the solution has to be dynamic.The groups are numbered from 1 to 6 and number of groups is fixed i-e 6.
A sample file illustrating the inputs and required output is attached.
I also posted this on: [URL] ....
Sample_file_summary.xlsx
I have a file with a lot of tables of different sizes. The table ranges have not been named. I would like to use a macro to insert 20 blank rows between each of them.
View 3 Replies View RelatedI need to insert a blank row between each row of data under my headers (not the immediate row) until I reach the end of the particular table. I then move down to the next header and repeat the process.
My starting data looks like this:
HEADER
DATAROW1
DATAROW2
DATAROW3
-emptyrow-
HEADER
DATAROW1
DATAROW2
DATAROW3
I need it to look like this:
HEADER
DATAROW1
-emptyrow-
DATAROW2
-emptyrow-
DATAROW3
-emptyrow-
[Code] .....
I have a spreadsheet that contains two input tables: Parallel and Perpendicular. Next, the user can select a group (from a drop down) which a certain entry belongs to. Now what I want is a "summary" of these entries in a table that has no blank rows and combines both Parallel and Perpendicular entries by the Group. There is no fixed number of inputs for any one group i-e the solution has to be dynamic.
Attached is a file illustrating the inputs and the required output. I would prefer a formula based approach but not against VBA based suggestions either.
Sample_file_summary.xlsx
Also posted at: [URL] ....
I have 90+ rows of data that I want to transfer into a table format on another worksheet. At present I have laboriously been copying and pasting from one to the other but am losing the will to live:
Id like to create a macro to do this for me. Is it a matter of recording the macro to replicate as I copy and paste or is there a better way....I have dabbled with a pivot table but cannot get it to do what I want.
Here is the code from a recorded macro that does one person/row of data but I dont know how to make this automatic for all rows of data (see below):
Sub staff_rpt()
'
' staff_rpt Macro
[Code]....
i have a macro which takes the results generated and puts them into a table and filters the data so that zeros does not show and arranges the table from largest to smallest. Now i have a problem where i need to select the power, fuel oil consumption, weight and area from one table and the other power, fuel oil consumption, weight and area from the other table and combine them together so that i can have all possible combinations of the data, but i don't want the zeros to show in the combinations. i need the combined data to show on a separate sheet. The data of table 1 varies with rows as shown:
The Data of Table 2 varies with rows as shown:
I would like to achieve a result like this in the following columns and rows without having to input them manually:
Using Excel 2010. I'm writing a macro that sets up a workbook to be used for estimating at the beginning of a project. In the code I need to create multiple tables (formerly known as "lists") in the workbook. Then later in the code I need to refer back to those newly created tables. Currently, the code that creates the table is part of a loop that creates the table on many different worksheets. The problem of course, is that I have to name the Table, and then it won't create a table of the same name on the next sheet. Then, later in the code, I need to make adjustments to the table that was just created before looping to the next sheet.
Is there a way to create a table without giving it a constant name? Or by giving it a name that builds off of other info in the sheet? For example, I would be good with the naming the table after the sheet name: "Sheet1_Table" or such.
Code:
Sub Auto_Open()
'
Dim sht As Worksheet
If Range("A1") = 1 Then
[Code].....
In the attached Excel file, the data has been place from d2 to dyo2.
For example, value "2607" is placed from cells va2 to wk2.
I need to merge cells from va2 to wk2.
I can't use horizontal alignment - center across selection because my boss wants it to merge only.
I have a list of data which doesnot match with my database and am hence failing to understand what to do.
2 sheets attached.
Was trying to find the employee ids of the advisors from the database.
I would like to open 3 excel files (with identical names) from 3 different mapped drives eg M,N and O. How can I use Excel VBA to rename these 3 files based on their original locations and save them in a folder in the C drive eg M_Filename,N_Filename and O_Filename.
View 3 Replies View RelatedI am retrieving data from 2 different sources in a database.
i am having problem with identical data being classed as different, see below.
******** ******************** ************************************************************************>Microsoft Excel - Book1___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutA3=
ABCD11.9.2.1**Access*Security***21.9.2.1**Access*Security***3FALSE***Sheet1*
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
These two cell are identical (have checked theer are no random spaces) however the formula is saying that they are not equal. They are also in identical formats.
I need a formula to sum column C if data in column A and Column B are found anywhere else in the list. For Example
A............B.......C
0103019..C.......5
0103019..1.......2
0103019..14.....1
0103019..C......4
Column D would show
9
2
1
9
=If(ISNA(VLOOKUP(B2, 'TableB'!$Y$2:$Y$100,1,FALSE)) , "",Column A in tableB)
I want to search tru all column B in table A. If i find an identical value in table B i want the field in table A to show the corresponding field in Column A in table B.
Also if there is 2 occurrences of column B in table B, i want the field in table A to show both values.
I have three large txt files. All three are the same number of columns but are of a different number of rows. (Each month the number of columns will increase but the 3 source files will still contain an equal number).
Therefore I need to open the first text file, select all data and paste it into my master file.
I then want to open the second txt file and paste all the data at the first empty row of my master file.
I will then open the third txt file and paste all the data at the first empty row of the master file.
Finally I will close the txt files.
I will then have several other amendments to make to the master data file.
So to my problem....
Opening the txt files is fine.
However I want to select all the data in the first txt file but excel gives me an error message. (On the line highlighted in my code). I remember there being a function in VBA that selects all data around a cell, can anyone remember how to do this?
My code so far is:
Workbooks.OpenText Filename:= _
"C:CUSTOMERPass 1.txt" _
, Origin:=xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier _
:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:= _
False, Comma:=False, Space:=False, Other:=False, FieldInfo:= Array(Array _
(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8 _
, 1), Array(9, 1))
Cells.Select
Selection.Copy
ActiveWindow.WindowState = xlMinimized
Sheets("Data").Select
ActiveSheet.Paste
This "If" wont work, does anyone have an idea why? I have try hard coded, with "" and without, i tried to assgind the field value to another double patameter and still nothing. This is my last try where i converted it to double type
View 7 Replies View Related