Questions regarding MS Excel & Word?
Ans 1 Create all the data in a single Sheet Method 1: Use Auto Filters, Filter out the each branch Data and copy the Filtered data into new Worksheet. to understand Filters visit: http://www.google.co.in/#hl=en&source=hp... Method 2: Create a Pivot Table (branch should be the first column-as i recommend for ease in extracting data) then double click the totals of Branch. this will create a new worksheet and provide that details of all the records for related branch. to understand Pivot Tables visit: http://www.homeandlearn.co.uk/excel2007/... Ans 2. you can use Inbuilt Mail Merge option of Msword. to understand more about Mail merge visit: http://www.gmayor.com/merge_labels_with_... Hope this will help you. (*.*)a
Is there a way to automatically extract a row of data in one sheet to another in Excel?
I think you have a VLOOKUP type of problem.For example, suppose cell A2 on Sheet1 contains a name. That same name appears somewhere on Sheet2 column A. In fact, Sheet2 has a bunch of really good data in cells A2:Z1000. You want to find that row, and copy the information for that person over to Sheet1.If my understanding is correct, you could use a formula in cell B2 like:=IFERROR(VLOOKUP($A2,Sheet2!$A$2:$Z$1000,COLUMNS($A2:B2),FALSE),””)This formula is designed to be copied across.
How can I copy an Excel cell to another sheet if a criteria is met?
Here’s the issue:Excel formulas cannot send data to other cells, sheets, etc. They can only receive data.So, cell “whatever” on Sheet 2 would have to hold a formula like this:=IF(Sheet1!A1="cat",Sheet1!B1,"")and cell “whatever” on Sheet 3 would hold this formula:=IF(Sheet1!A2="bear",Sheet1!B1,"")To “vector” data like you ask would require VBA code.
How do I delete an entire row in Excel based on duplicates of one column?
I suggest adding an auxiliary column to your worksheet that returns TRUE if the email is not a duplicate. You can then sort your database by this auxiliary column and delete the duplicate rows.1. Assuming the emails are in column C and column D is the "no dupe" auxiliary column, you would populate cell D2 with a formula like:=COUNTIF(C$2:C2,C2)<22. Copy the preceding formula down. The easy way to do this is to doubleclick the little square at bottom right of the selection marquee surrounding cell D2.3. While the auxiliary column is selected, Copy it4. Replace the formulas with the values returned using Paste Special...Values5. Select your database (including header labels) in cells A1:Dxxx6. Use the Data...Sort menu item to sort by column D7. The duplicate rows will have a value of FALSE in column D, and will be at the top of the list after sorting. Select those rows and delete them.8. Delete column D
If a cell contains a certain text (Eg: Apple), how can I return the entire row to the same row on another Excel sheet?
If you want to go down the macro route then you'll need some sort of loop with a nested IF inside. Do until activecell.value ="" If lcase(activecell.value) like "*apple*" then X=activecell.row Rows(activecell.row).copy worksheets(2).rows(x) End ifActivecell.offset(1,0).selectLoopI don't know if you've used macros before so I've kept it simple but this tests the cell to see if it contains the word apple using wildcard characters either end. By using lcase it converts the contents to lower case for the purpose of the IF statement which helps deal with data entered in either lower or upper case. The variable x stores the row number it finds apple in and copies over to sheet 2 to the same row number. So now you have a couple of options.
How can you pull data from one sheet to another in Excel based on a particular value?
1- Vlookup function2- Index & Match function “=INDEX (column to return a value from, (MATCH (lookup value, column to lookup against, 0))”I prefer IndexMatch function for many reasons:1- IndexMatch works in two directions (right to left and left to right) while vlookup cannot look to its left.2- If you want to insert or delete columns from the table or sheet you are getting data from the Vlookup function will get broken or gives wrong results, and this doesn’t happen in IndexMatch.3- If you are working with big data like thousands of rows that means thousands of Vlookup formulas, Excel will work much faster if you use IndexMatch rather than Vlookup.
How can I pull data from one Excel sheet to another automatically? I want to run a comparison on products I've sold one quarter versus another. Is there a way I can pull in the sales quantity from the other sheet and add it to the row to compare?
There are two ways to do this. Use VLookup or Use Combination of Index-Match.Lets say you have your data in two sheets, X and YPlease note that the same products might not be selling in all the quartersNow, when I want to consolidate in a separate sheet, I would first of all put all product categories in that sheet.Post that, I can use Vlookup to find the value. The only problem is that if the value does not exist, I would get a #N/A error. To avoid that you can use iferror to make the value 0, if it is not found. Please note that the array where you are looking for the key, has to be protected with $sSimilarly I can use a combination of Index and Match, protect the key column with $ and ensure that I can avoid #N/A errors with iferror
I want to transfer my data from sheet 1 to sheet 2 on the basis of one particular value in one column and if I add some data in sheet 1 it automatically updates in sheet 2 (Excel). What should I do?
You can give cell reference in the second sheet.How to give cell reference?Go to second sheet.Select the cell which you want to update by sheet1 value.Now press = in the cell of sheet2. After pressing = you will be able to select the value of origing cell, you want to reflect here.That's it.. You can give reference of cells in same sheet, cell of different sheet or workbook.Now excel will take the updated value whenever you update your base cell.Hope this helps,Regards,
How can I mirror a few specific rows from one sheet to another sheet in Google Sheets?
Consider the spreadsheet below is your sheet1 and you want to copy all the rows where the object is equal to mirror.Then on sheet2 add the formula=filter(Sheet1!A:Z,Sheet1!A:A="mirror")Sheet1!A:Z - data contained in sheet1 from column A to Z.Sheet1!A:A="mirror" - filter rows that contain the word mirror on column A.The result is:Read more: 17 Essential Tricks for Google Sheets