Windows Support Forum

Solved: Comparing/Matching data across in Excel worksheets & more

Q: Solved: Comparing/Matching data across in Excel worksheets & more

Hi Hopefully one of you delightful people can help Comparing/Matching Excel & worksheets more in across Solved: data me with a small problem I m having pulling together data from multiple reports I have manually combined separate reports Solved: Comparing/Matching data across in Excel worksheets & more into workbook with worksheets for each report Each row represents a separate job The st worksheet is an export from our own Access database the others come from external reports Whilst each report worksheet contains different sets of data column in each worksheet contains amp CSR amp or amp Site ID amp numbers These numbers can be used to match the jobs - however a not every worksheet has every number b some numbers may appear more than once on the same worksheet these are essentially returns to the same job The st Solved: Comparing/Matching data across in Excel worksheets & more worksheet also has a column with OPID numbers Column A these are unique numbers that we have paired to a CSR or Site ID number I want to compare the numbers in the CSR column of the st worksheet Column B of Sheet with the CSR columns in the other worksheets and where there is a match copy add the corresponding OPID number to a blank column in each worksheet What would be the best way to do this Thanks in advance for any help and advice offered nbsp

Relevancy 100%
Preferred Solution: Solved: Comparing/Matching data across in Excel worksheets & more

I recommend downloading and running Reimage. It's a computer repair tool that has been proven to identify and fix many Windows problems with a high level of success.

I've used it in the past to identify and fix everything from blue screens (BSOD's), ActiveX errors, corrupt files and processes, dll/exe/sys errors, recover lost memory, Windows update problems, defragging, malware removal etc.

You can download it direct from this link (This link will automatically start a download of Reimage that you can save to your computer.)

A: Solved: Comparing/Matching data across in Excel worksheets & more
Relevancy 88.97%

I'm using Excel 2007, Windows vista. I need to compare actual costs (sheet 1) and estimated costs (sheet 2) to get the cost variance (sheet three) I'm getting really wacked out numbers using consolidation, but I have no idea what I'm doing

A:excel - comparing two worksheets to get data for a third

May I assume that the rows in Sheet1 and Sheet2 are not the same?
There must bbe a common denominator against which you want / can compare the costs?
A simple sample would help give the picture of the actual situation.
Relevancy 102.34%

Hi Guys,

I have an Excel 2007 Workbook with two Sheets.

Sheet1 is Info pulled from a SQL DB Table. It shows Residential and Postal codes for suburbs. Both residential and postal codes appear in one column.

Sheet2 is info pulled from the Post Office Website. It shows Residential and postal codes. However, it displays them in two seperate columns.

Therefore, Sheet1
Suburb City code
---------- ------ -------

Sheet2 :
Suburb City Residentialcode postal
---------- ----- ----------------------- ---------
I need to compare these data as the data from the SQL DB(Sheet1) may be outdated. Sheet2 is from the post office as

Is there anyway to compare them?

Relevancy 101.05%


I need to compare 2 different Excel spreadsheets (each spreadsheet has at least one column of data which is the same) When I get a match I want to be able to then say if a match occurs then show the data from another cell. The main priority is how to compare the 2 spreadsheets. I think it is something to do with VLOOKUP but I am a complete novice so please help if you can.

A:Excel - comparing 2 separate worksheets

It's easy to do if the rows and column numbers are the same but once they are throughout the column with the same vale it gets weird. Try a pivot table to do it. I think you are getting into the realm of a database with what you are trying to do. Try a simple databse and import you data into different tables for each spreadsheet and run a query on the two fields that contain the same data.
Relevancy 93.31%


First, thank you all for you time and knowledge in advance.

Here's my situation. I'm working with file that is sent to me with populated information, in this example Col A
What I would like to do is enter information in Col B and then do a comparison of both A and B, letting me know if something in Col B is not in Col A and also is something in Col A not in Col B.

I need to know if what was sent to my in the spread sheet match what I physically inventoried.

I have provided an example.

Hopefully I did not over complicate my request.

Thank you,


A:Solved: comparing data in two columns in Excel

does this help

or this may ble closer to what you need
Relevancy 92.88%

I m in a bit of a pickle I have an excel document with a summary page quot Tracker quot This worksheet has a table in it with a whole heap of customer information and at the end there are columns for revenue summaries Every month I run a report that I add in a new worksheet which is called quot Relevant Month Revenue Report quot and then I do a vlookup to transfer the revenue and product information in columns quot C quot and quot D quot of the report into the relevant month columns in the quot Tracker quot This has been working well for me but i ll be handing this role over soon so I to Solved: worksheets one into Macro data transfer Excel several from was wondering if there was a macro that I could use that would do this vlookup automatically for the relevant month The Solved: Excel Macro to transfer data from several worksheets into one information in the reports is not in any particular order so i can t do a simple copy paste into my quot Tracker quot summary Any help would be greatly appreciated nbsp

Relevancy 92.88%

Hi All What I m trying to do is type lists in Excel using the version so that an amount can be summed in separate worksheets and then the list to be consolidated into in Excel? from Solved: worksheets Consolidating data multiple another worksheet so all the items can be seen at a glance I would also like to Solved: Consolidating data from multiple worksheets in Excel? make it so that when the data changes in one worksheet the consolidated view updates accordingly An example would be for say a shopping list where the items are broken down into their respective storage areas So there would be a worksheet for Fridge Freezer Top Solved: Consolidating data from multiple worksheets in Excel? Cupboard Bottom Cupboard etc Each of these would contain a number in Column A and then the name of the item Solved: Consolidating data from multiple worksheets in Excel? in Column B with each item having its own row E g Apples followed by Oranges on the next row There would also be a SUM function on each worksheet to total the items on that sheet Once all the data was entered on the separate sheets the consolidated data would appear in the format below commas separate column data Name of Place Fridge Data E g pts of Milk Eggs Blank Row Name of next place Freezer Data E g Packs of Bread Ready Meals Blank Row Name of next place etc Hopefully this consolidated view would also update so that if for instance quot Butter quot was added under quot Eggs quot then the quot Butter quot would not go into the blank row but would add itself above the blank row on its own new row I m sure that this has to be possible just I lack the know-how Hopefully I can get some help here Thanks nbsp

Relevancy 83.85%

Not sure where to post this; hope I found the right forum.

I have a set of texts in the source language in Excel. I have translated these into the target language. What I now want to do is match the source language data with my translations.

Is there any way to tell Excel that "when the text XXX is in column G, row 341, the text XXX is to be inserted into column H, row 341)?

Just an example.

In other words, what I want to do is make sure my translations are placed in the column to the right of each source language text.


A:Excel, matching data in rows

i have moved to business applications

in column H you can use IF

in H341
=IF(G341="XXX", G341, "what to do if not")

this will copy G341 into H341 only if G341 contains "XXX"

=IF(G341="XXX", "YYY", "what to do if not")

if what you want inserted is different text
Relevancy 83.42%


I have the following challenge: I need to compare two excel files and check if certain identification numbers of participants in the one file are also present in the second file. The first file contains the identification numbers of the people who were invited to complete a questionnaire (SampleMapLookUp) and the second file contains the data of the questionnaires that were sent back (SampleFileBase). Is there a way to automate this process? In annex two sample files: SampleMapLookUp is the file with the numbers (name of the column is "ID_IND") that I want to check in the second file: SampleMapBase (name of the column here is "his_1").
I don't know if it was necessary but for testing sake I marked the numbers that should be found in yellow in the SampleMapBase. The resulting file should ideally have the lay-out and content of SampleMapLookUp.

Thanks for any advice,


Relevancy 82.99%

I grade students on a spectrum and I have a separate page set up for each student with every assessment. So, for example, a student might have an "x" under Approaches Expectations or Meets Expectations. I would like to be able to collect data from all the worksheets in one place so that I can analyze performance by the whole group of students on any one given assessment.

Let me know if you need more information to figure this out.

Relevancy 82.99%

I am trying to use a formula or Excel "tool" to extract data from several worksheets into a new summary worksheet. The problem is, I don't know which Excel tool or formula would be best.

Each worksheet represents a committee, and includes an archived list of agenda topics that have been discussed/reviewed by that committee, by date. I want to create a summary worksheet that shows a list of each agenda topic, then shows an "x" next to each in which committee that topic has been discussed/reviewed == this is where I will need to use a formula or tool to pull the data from the other worksheets.

Can anyone help?? If so, can you please list step by step instructions, I took an advanced excel class but cant remember most of what I learned there so I am really only a basic user. Much thanks and gratitude!

A:Extracting Data from Excel Worksheets into New Worsheet

Welcome to the board:

I think you should look up some sites with examples or tips, I can advise these:
Specific stuff like filetering and adding x's etc is something for later and deeper vba and formula's
Relevancy 82.99%

I have an excel file in which I am needing to creating multiple tabs. I want to have it where when I make any changes to the main tab that the data auto populates into the other tabs which I have filtered.

Relevancy 82.99%

I'm looking to generate an overview table of data stored in a number of worksheets.

The data I am retrieving values from is unordered eg:

Worksheet - April
Col A - date
Col B - invoice number
Col C - department code
Col D - description
Col E - Credit
Col F - Debit

The results are based on the corresponding values of the department codes and show the yearly spend for each department with individual transactions. The results sheet is formatted like this:

Worksheet - overview
Col A - Date
Col B - invoice number
Col C - description
Col D - Credit
Col E - Debit

So, if April Col C = 2211,7300,5004,6002,4001,4906,4000,8204,4000,4000,5004,7302
and I want the overview sheet to show all transactions throughout the year in code 4000 as well as return all corresponding data which formula would I use?

any help gratefully recieved.

Many thanks


A:Excel - pull out data from multiple worksheets

Relevancy 82.99%

Hello I need to sort extensive data from sheets two then excel VBA matching help copying - Need and with data and am facing the following problem There are two sheets Sheet and Sheet Columns in Sheet Need help with excel VBA - matching and then copying data from two sheets are the following company PERMNO number dates market capitalization no data in this one Columns in Sheet are the following company Need help with excel VBA - matching and then copying data from two sheets PERMNO number ticker dates market capitalization In Sheet there is a whole range of dates between and for every single company whereas in Sheet there is one or - dates for a every single company I would like to match the exact date dates in Sheet in the range of dates in Sheet and then if they match copy the corresponding cell value from the market capitalization column in Sheet to the empty market capitalization column in Sheet I also want that the company PERNO numbers match In short if PERMNO numbers match match the date dates in Sheet within the range of dates for the same company in Sheet and copy the market capitalization value to Sheet Example Sheet A B C PERMNO DATES MARKETCAP Sheet A B C PERMNO DATES MARKETCAP When we match the date from sheet - and within the range from Sheet we copy market cap values from Sheet to Sheet PERMNO from Sheet should match with PERMNO from Sheet Thank you very much for your help Highly appreciated nbsp

A:Need help with excel VBA - matching and then copying data from two sheets

Off the top of my head, I would think all you need to do is use the built-in "find" function. Look it up. Make sure sheet 2 is sorted before you use the find.

For each PERMNO in sheet1, do the find function on sheet2. When it finds a match it returns the row of the FIRST match in sheet2. At this point, loop through the rows in sheet2, starting on the row that the find function returns, until you hit a PERMNO that is not the one you are looking for. This will work only if sheet2 is sorted. On each of those iterations you can check for the matching date, and on that match you can do the copy of the marketcap.

In VBA, you have to be very careful about which sheet you are referencing. So make sure to always put sheet1.cells(3,4) instead of just cells(3,4).

Hope this helps somewhat.
Relevancy 82.56%

hi guys, I have different data sets from two different software programs these two programs share data.these are bookkeeping entries. debet creditbank 100bank1 200.18soft 100soft 200.18the first data set consist of the last two entries of soft but then like a a lot of entries of soft. the second data set consist als of soft debet creditsoft 300.18person1 100person2 200.18i have to match soft of the first data set that consist of soft 200.18 and 100 with the second data set dat consist of the same soft but the sum of the individuals soft.can sombody give me an idea on how to look at these problems in excel.

A:excel challenge comparing two data sets

We probably have a language issue here, so it may a few tries before we can offer a solution. At this point I am totally confused by your request. Perhaps you could post some more examples of your input along with the desired output. We want to help, really we do!Please click on the following line and read the instructions on how to post example data in this forum. Thanks!Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.
Relevancy 82.56%

I am having two column with current month profit & last month profit respectively Column A & Column B. I would like to compare both column & set the ICON (down Arrow & up Arrow ) based on result if A1 value greater than B1 set upper arrow else down arrow

A:How to set ICON in Excel by comparing data in 2 Columns

You didn't say where you wanted the arrows to appear, so I'll use C1 for this explanation.1 - In C1, enter this formula: =IF(A1>B1,2,IF(A1=B1,1,0))2 - Under the Conditional Formatting drop down, choose Icon Sets and then choose the Up arrow.3 - Go back into Conditional Formatting and choose Manage Rules4 - Edit the Rule for C1 as follows:----- Click the Show Icon Only box to place checkmark in box----- Change Type to be Number in both dropdowns----- Up Arrow: when value is >=2----- Sideways Arrow: when <2 and >0----- Down Arrow: when <=0 (should be default)5 - Click OKLet me know how that works for you.Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.
Relevancy 82.56%

Hi All,

i want to compare data between two work sheets of one workbook and if data matches then it will be copied to third work sheet automatically. can some body help me.


Relevancy 81.27%

Hi everyone. I am starting my own business and would like to use an invoice template I created in Excel. The problem I am having is this... I have three worksheets, one is the actual INVOICE, one is for TOTALS, and one is for collecting the CUSTOMER data from the invoice. I have all the formulas linked and working, however, here is what I am trying to achieve:

First, I would like for the invoice template to regenerate a new invoice number each time I open it, then I would like for worksheet two (TOTALS) to be able to gather the totals from each invoice (info from invoice 1 on line 1, invoice 2 on line 2, etc) as well as have running totals at the bottom, and for worksheet three to continually add a line for each new invoice with that customer's data. Is this achievable? Thanks!

Relevancy 81.27%

I would like to get a script going that checks column A in an Excel spreadsheet with the contents of another file. The other file can be separate Excel file or a txt file. The script would delete the entire row of data in the main spreadsheet when the data (string) in column A matches anything listed in the txt (or other Excel) file.

I'm a real beginner and appreciate any help you can give me.
Relevancy 79.55%

In cell j, I have formula =IF(SUMPRODUCT(ISNUMBER(SEARCH("VLXP",K2:AB2))+0)>=1,"Yes","No") that returns yes or no if VLXP is contained in any cell K2 through AB2 and it works correctly. What I would really like to do is then put into cell j the entire matching cell content or if not found return n/a. Is there a way to accomplish this maybe with VBA?

A:Solved: Excel if cell contains vlxp then put matching cell data in current cell
Relevancy 75.68%

Hi guys I have been on the lookout for a macro that will compare worksheets within MS Excel and output the differences on a third sheet I am being directed to the URL below but cannot for the life of me work out how Excel in to Macro this How (compare MS worksheets) use excel Solved: to implement this http exceltip com st Compare two worksheets using VBA in Microsoft Excel html There are two snippets of code which I will include here for convenience but not sure whether these should be pasted into Workbook Code i e right-clicking Excel icon left of quot Solved: How to use this Macro in MS Excel (compare excel worksheets) File quot - top-left Worksheet Code i e right-clicking worksheet tab or inserting a new Module - Solved: How to use this Macro in MS Excel (compare excel worksheets) or some other method Is there anyone here that could guide me to how to implement this code Quote from webpage - quot With the macro below it is possible to compare the content of two worksheets The result is displayed in a new workbook listing all cell differences quot Code Sub CompareWorksheets ws As Worksheet ws As Worksheet Dim r As Long c As Integer Dim lr As Long lr As Long lc As Integer lc As Solved: How to use this Macro in MS Excel (compare excel worksheets) Integer Dim maxR As Long maxC As Integer cf As String cf As String Dim rptWB As Workbook DiffCount As Long Application ScreenUpdating False Application StatusBar quot Creating the report quot Set rptWB Workbooks Add Application DisplayAlerts False While Worksheets Count gt Worksheets Delete Wend Application DisplayAlerts True With ws UsedRange lr Rows Count lc Columns Count End With With ws UsedRange lr Rows Count lc Columns Count End With maxR lr maxC lc If maxR lt lr Then maxR lr If maxC lt lc Then maxC lc DiffCount For c To maxC Application StatusBar quot Comparing cells quot amp Format c maxC quot quot amp quot quot For r To maxR cf quot quot cf quot quot On Error Resume Next cf ws Cells r c FormulaLocal cf ws Cells r c FormulaLocal On Error GoTo If cf lt gt cf Then DiffCount DiffCount Cells r c Formula quot ' quot amp cf amp quot lt gt quot amp cf End If Next r Next c Application StatusBar quot Formatting the report quot With Range Cells Cells maxR maxC Interior ColorIndex With Borders xlEdgeTop LineStyle xlContinuous Weight xlHairline End With With Borders xlEdgeRight LineStyle xlContinuous Weight xlHairline End With With Borders xlEdgeLeft LineStyle xlContinuous Weight xlHairline End With With Borders xlEdgeBottom LineStyle xlContinuous Weight xlHairline End With On Error Resume Next With Borders xlInsideHorizontal LineStyle xlContinuous Weight xlHairline End With With Borders xlInsideVertical LineStyle xlContinuous Weight xlHairline End With On Error GoTo End With Columns quot A IV quot ColumnWidth rptWB Saved True If DiffCount Then rptWB Close False End If Set rptWB Nothing Application StatusBar False Application ScreenUpdating True MsgBox DiffCount amp quot cells contain different formulas quot vbInformation quot Compare quot amp ws Name amp quot with quot amp ws Name End Sub Quote from Webpage - quot This example macro shows how to use the macro above quot Code Sub TestCompareWorksheets ' compare two different worksheets in the active workbook CompareWorksheets Worksheets quot Sheet quot Worksheets quot Sheet quot ' compare two different worksheets in two different workbooks CompareWorksheets ActiveWorkbook Worksheets quot Sheet quot Workbooks quot WorkBookName xls quot Worksheets quot Sheet quot End Sub Thanks in advance DarkKman nbsp

A:Solved: How to use this Macro in MS Excel (compare excel worksheets)

Apologies... I have sorted this... For those that might be looking at this for help here's what I missed:

These are both Sub's that should be created as a Module - both in the same module. I am just comparing worksheets within the Active Worksheet so just removed:

' compare two different worksheets in two different workbooks
CompareWorksheets ActiveWorkbook.Worksheets("Sheet1"), _
Click to expand...

I was then able to run this from Macro's. All sorted.

Apologies again
Relevancy 73.53%

Please help. I've been racking my brain trying to figure out how to get excel to compare to columns of data and return the exceptions. Can someone please assist me with either formula or function I need to do this?

In the spreadsheet attached, I have two tabs, Source and Compare. I need the formula or function to compare the data on tab 'Compare' column A to the original data on tab 'Source' column and return the exception.

End result should be a list that reflects data on Compare tab that is not on Source tab.

Relevancy 73.1%

I have an Excel workbook that contains two worksheets. There are formulas on worksheet two that brings data from worksheet1.

I have code that runs when a button is pressed to strike through data on worksheet one. Does anyone know a way to strike through the respective data on worksheet 2 as well?

Relevancy 73.1%

I need help..

I have two worksheets (ABLE and BETA) and would like to search for duplicate data in colume A in both worksheets. If there is a match, copy data in worksheet ABLE row E to worksheet BETA row E. Also if it finds more than one of the same duplicates create a new row and copy the data from worksheet ABLE row A and E to BETA row A and E.
Thanks for your help.

Relevancy 72.67%

not sure adding data by Solved: totals matching if this is possible to do i m looking for a macro that would look at the information in the excel sheet and add up the totals for the matching rows Excel sheet data Code Code Project Category Action Total Multi GR-Inside Design Multi GR-Inside Design Graphics GR-Inside Design Graphics GR-Inside Design Multi DUP-Quick Color Multi GR-Inside Design Multi GR-Inside Design Multi DUP-Quick Color Solved: adding totals by matching data Multi GR-Inside Design Multi TE-Inside Edit Graphics GR-Inside Design Graphics GR-Inside Design Graphics GR-Inside Design Graphics GR-Inside Design Solved: adding totals by matching data I would like to macro to look at the quot code quot quot project quot quot category quot name and quot action quot name add up the totals into line add a count for how many lines were matched delete all duplicate lines after adding up the totals and counting how many lines were there So the above data would come out to this Code Code Project Category Action Total Count Multi GR-Inside Design Graphics GR-Inside Design Multi DUP-Quick Color Multi TE-Inside Edit Graphics GR-Inside Design the above is just a small sample of over lines so you can see the reason why i would like to automatic this process any help would this would be great nbsp
Relevancy 72.24%

This is somewhat complicated to explain but here goes I have two workbooks one is very complicated with many sheets covering days of events Excel matching Solved: question In Book each sheet has listed batches in a column labeled Batch as you can see in the attached image The batch number is in Column C I have another workbook Book generated from another source that has the same batch numbers in a Solved: Excel matching question column labeled Batch In the first workbook the batches are mostly in sequential order but not always and may be on a different day s sheet whereas the nd book s batch numbers are always in sequential order What I want to do is create columns in Book and import columns from Book the batch number and the corresponding Good number and Solved: Excel matching question here s the kicker I want it to find the correct matching batch number and align the imported data with each Does anyone know what formula macro I could use to do this Thanks nbsp

Relevancy 72.24%

Is there a way to compare Excel 2002 worksheets and cross-reference them?

Relevancy 71.81%

Just a little background I suck at Excel I mostly work on mainframes these days doing data processing Our data processing always deals with Text files but just recently we have a client who would like to receive their Data back in an Excel format That is not a big issue We can easily take a text file and import it into Excel The problem comes into play when they would like their data split by a LOT Number so that each Lot Number is on its own Worksheet Now that is not that big of a deal if there were just a few Data Multiple WorkSheets Solved: from to Split Sheet1 Lot Numbers I can turn the filters on for the Lot Number Column and copy and paste to a new Worksheet But most of the time we are dealing with a few dozen lot numbers and usually rows So I Solved: Split Data from Sheet1 to Multiple WorkSheets found a somewhat functional VB Macro to split the data to multiple worksheets but it has a few drawbacks I will post the code I am using at the end of this Drawbacks It doesn t copy and paste the header row to each new worksheet The Columns it copies is hard coded I could on any given order I do for them have Column A-M populated or sometimes it could be columns A-P Is there anyway to make this more flexible so that it can just look at the header row and see that the last column that is populated is where my data ends for the columns I don t like having to edit the ending column every time in the Macro Each new sheet name is named by the Lot number it finds in Column A That is fine but I actually need the Worksheet name to say LOT DATA Currently the Worksheet name just says which is the Lot Number So basically it needs to Prefix the word LOT before the Lot Number and append the word DATA after the Lot Number I am so horrible with Excel And it got worse with Excel I stumble around in it How do I save this code so that it is not part of the Workbook with the data I am working on and is available anytime I open Excel Here is the Macro I am currently using which I found over on StackOverFlow Code Sub SplitData Dim DataMarkers Names As Range name As Range n As Long i As Long Set Names Range quot A A quot amp Range quot A quot End xlDown Row n DeleteWorksheets For Each name In Names If name Offset lt gt name Then ReDim Preserve DataMarkers n DataMarkers n name Row Worksheets Add After Worksheets Worksheets Count name name n n End If Next name For i To UBound DataMarkers If i Then Worksheets Range quot A C quot amp DataMarkers i Copy Destination Worksheets i Range quot A quot Else Worksheets Range quot A quot amp DataMarkers i - amp quot C quot amp DataMarkers i Copy Destination Worksheets i Range quot A quot End If Next i End Sub Sub DeleteWorksheets Dim ws As Worksheet activeShtIndex As Long i As Long activeShtIndex ActiveSheet Index Application DisplayAlerts False For i ThisWorkbook Worksheets Count To Step - If i lt gt activeShtIndex Then Worksheets i Delete End If Next i Application DisplayAlerts True End Sub Any help would be appreciated nbsp

Relevancy 71.81%


I have a list of customer IDs in two columns. I want to generate the customer IDs which are missing in either of the two columns in a third column. What formula can be applied here?

Thanks in advance

Relevancy 71.81%

I have a spreadsheet that I wish to compare 2 columns and bring the information from the 3rd. For example, if A6 is equal to an item in G1:G98, then the number in B6 should equal the corresponding number in H1:h98. The formula I used is =VLOOKUP(A8,$G$1:$H$98,2,FALSE) It works great except the problem I have is sometimes there is no corresponding value in G1:G98. In that case I would like it to enter the number 0.

A:Solved: Excel comparing 2 columns

Hi Bsoda,


(looking at what I just posted, its displaying an extra space in the final FALSE, but its not there when I go to Edit it!!)
Relevancy 71.81%

Hi I am using a code to compare two spreadsheet One of the function is to copy the modified records in a different worksheet highlighting changes element in red The script works but ONLY when Excel VBA spreadsheet comparing Solved: the cell is modified to quot blank quot no value I get coloured in red the upper cell instead of the correct one Here the script any help Sub GDV Dim WsA As Worksheet WsB As Worksheet WsC As Worksheet WsD As Worksheet WsE As Worksheet Dim rFind As Range c As Range Dim I As Integer ColCnt As Integer Set WsA Worksheets quot OldExport quot Set WsB Worksheets quot NewExport quot Set Solved: Excel VBA comparing spreadsheet WsC Worksheets quot Changes quot Set WsD Worksheets quot PosDeleted quot Set WsE Worksheets quot PosAdded quot ColCnt WsA Cells Columns Count End xlToLeft Column With CreateObject quot Scripting Dictionary quot For Each c In WsA Range quot A quot WsA Range quot A quot amp Rows Count End xlUp If Not exists c Value Then Add c Value False Set rFind WsB Columns Find What c Value LookIn xlValues If Not rFind Is Nothing Then For I To ColCnt If Not c Offset I - WsB Cells rFind Row I Then If Item c Value False Then rFind Resize ColCnt Copy WsC Range quot A quot amp Rows Count End xlUp Offset Item c Value True End If WsC Cells Rows Count I End xlUp Interior ColorIndex End If Next I Else MsgBox c Value amp quot PosID has been canceled quot c Resize ColCnt Copy WsD Range quot A quot amp Rows Count End xlUp Offset End If End If Next c For Each c In WsB Range quot A quot WsB Range quot A quot amp Rows Count End xlUp If Not exists c Value Then MsgBox c Value amp quot PosID has been added quot c Resize ColCnt Copy WsE Range quot A quot amp Rows Count End xlUp Offset End If Next c End With End SubClick to expand nbsp

A:Solved: Excel VBA comparing spreadsheet

to who may be interested
I found a solution (workaround) here:
Relevancy 71.81%


I've been trying to find a shortcut for this, surely one exists...

I have 2 excel files. The first is a company stock listing, listing in Col A the stock code, Col B the quantity on hand. In the 2nd file, I have a column listing the same code, and another column listing a unit price. My goal is to create a sheet that lists both cost and quantity, but there are items in one that aren't in the other, and vice versa, so I can't just do a sort and copy/paste accross from one to the other. My boss wants me to do it manually, but there are about 3000 line items!

If someone could suggest a shortcut here I would be extremely grateful! If more information is needed, let me know.

Thanks in advance!

Relevancy 71.81%

My company uses Excel for our timesheets. In the same file, I have 12 worksheets, one for each month. I want to add a 13th worksheet so I can automatically tally my vacation days.

Whenever I use a vacation day, it gets added as 8 hours on that day. Each month tallies up the vacation days used for that month in the same cell coordinate, Row 55/Column AJ.

I can't figure out how to create an equation that spans multiple worksheets. Can someone tell me the equation that I need that does the following computation?

I need it to add up all the numbers for each of the 12 monthly worksheets in cell 55AJ and then divide that total by 8 to give me the total vacation days currently used.


at least just sum up all the numbers for cell 55AJ from the 12 worksheets. I can easily divide that number to get the day breakdowns.

Thanks in advance.

Relevancy 71.81%

I need to move data from a second worksheet to the first. See attached example. The second worksheet will be changing weekly (the real data will be about 18000 records), so I'd like the formulas to be in place with minimum intervention. I need to pull the AgentSet off of the second worksheet (ServerDump) and put it in Customer Form (Type of Agent). I would like to say 'BaselineAgent' if it's true. I've tried different formulas and think I should be using vlookup but can't get it to work. Any suggestions or examples of coding I could use? Thanks!

Relevancy 71.81%

How can I change the format in one excel 2007 file so that all the sheets are in landscape format? I would like this to be for only one particular file. Thanks.

Relevancy 71.81%

I have the following code Code Private Sub Workbook Open Dim myCount Worksheets To Macro Solved: That Excel All Applies Dim i Dim ws Solved: Excel Macro That Applies To All Worksheets As Worksheet Set ws ActiveSheet Application ScreenUpdating False myCount Application Sheets Count Sheets Select 'This line of code selects the st sheet For i To myCount ws Unprotect quot test quot ws Range quot A A quot FormulaHidden True ws Range quot B quot FormulaHidden True ws Range quot B quot FormulaHidden True ws Range quot B quot FormulaHidden True Solved: Excel Macro That Applies To All Worksheets ws Range quot B B quot FormulaHidden True ws Range quot C quot FormulaHidden True ws Range quot C quot FormulaHidden True ws Range quot D E quot FormulaHidden True ws Range quot D quot FormulaHidden True ws Range quot E quot FormulaHidden True ws Range quot E quot FormulaHidden True ws Range quot E quot FormulaHidden True ws Range quot F quot FormulaHidden True ws Range quot F quot FormulaHidden True ws Range quot G G quot FormulaHidden True ws Range quot G quot FormulaHidden True ws Range quot J J quot FormulaHidden True ws Range quot J J quot FormulaHidden True ws Range quot J J quot FormulaHidden True ActiveWindow ScrollColumn ActiveWindow ScrollRow ws Range quot A quot Select ws Protect DrawingObjects True Contents True Scenarios True Password quot test quot If i myCount Then ActiveWorkbook Save End End If ws Next Select Next i Application ScreenUpdating True End Sub The code runs fine on the first worksheet but errors out on all the other worksheets at the same line Code ws Range quot A A quot FormulaHidden True nbsp

A:Solved: Excel Macro That Applies To All Worksheets

I was able to fix the issue by modifying the code.

Private Sub Workbook_Open()

Dim myCount
Dim i
Dim ws As Worksheet

Application.ScreenUpdating = False

myCount = Application.Sheets.Count

Sheets(1).Select 'This line of code selects the 1st sheet

For i = 1 To myCount

[B]Set ws = ActiveSheet[/B]

ws.Unprotect "test"

ws.Range("A55:A58").FormulaHidden = True
ws.Range("B35").FormulaHidden = True
ws.Range("B43").FormulaHidden = True
ws.Range("B47").FormulaHidden = True
ws.Range("B50:B52").FormulaHidden = True
ws.Range("C35").FormulaHidden = True
ws.Range("C43").FormulaHidden = True
ws.Range("D49:E49").FormulaHidden = True
[B]ws.Range("D55:E55").FormulaHidden = True[/B]
ws.Range("E35").FormulaHidden = True
ws.Range("E38").FormulaHidden = True
ws.Range("E44").FormulaHidden = True
ws.Range("F52").FormulaHidden = True
ws.Range("F58").FormulaHidden = True
ws.Range("G4:G26").FormulaHidden = True
ws.Range("G49").FormulaHidden = True
ws.Range("J4:J26").FormulaHidden = True
ws.Range("J30:J31").FormulaHidden = True
ws.Range("J38:J39").FormulaHidden = True

ActiveWindow.ScrollColumn = 1
ActiveWindow.ScrollRow = 1

[B] ws.Range("A3:J3").Select[/B]

ws.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, Password:="test"

If i = myCount Then
End If
Next i

Application.ScreenUpdating = True

End Sub
Relevancy 71.81%

I recently took responsibility of a large amount of data in several Excel 2010 workbooks. The previous person was saving a copy of each workbook that alphabetized the worksheets. There are no macros in place. I know she was doing it, I just can't figure out how. Any solutions?

Relevancy 70.95%

Hello all. I have 2 spreadsheets which contain subscription information for 2 different products costing 21.58 and 5.96. I'm trying to merge this information so that if Mr A in spreadsheet 1 is shown as subscribing to Product B and the same person in spreadsheet 2 is shown as subscribing to Product C, an additional column shows in spreadsheet 1 so that it then appears as: Mr A - 21.58 - 5.96

If not, the columns would show Mr A - 21.58 - 0 (or blank) etc

The code for VLOOKUP is straight-forward but only returns the first row. I need it to reference every row and cross-reference the data.

I'm using Windows 7 and Excel 2010. I've attached a small example though the actual spreadsheet has around 3000 rows. I only need to do this once to get a single file up and running.

Any ideas

Many thanks


A:Solved: Comparing multiple values in excel

Sorry all. Now solved using a simple VLOOKUP and defined names with a fill-down. Doh!
Relevancy 70.95%

Thanks to everyone who helped me with that last one.

What I'm stuck on now is an array that brings up all of he rows that contain a certain policy number in column A.

So Say We have;

TL123 X B A 3C
TL456 C B E 4D
TL586 X B A D4
TL456 C B A 3C
TL892 C A E 4F
TL586 C A E 2B

And someone here say put the value TL586 in cell m6 they would get an answer of;

TL586 X B A D4
TL586 C A E 2B

The current sheet is approximately 500 rows by 30 columns and will be growing daily, so running this through IF Calculations is taking about 2 minutes to recalculate each time, so that isn't viable at the moment.

Any help appreciated,

Relevancy 70.95%

I have an Excel program that starts at worksheet A then depending on the users responses could navigate to B then C then D or could go from A to D to B etc I would like code that would navigate to previous sheets in Navigating Worksheets to Solved: activated previously Excel the order in which they were activated by users For example if the user navigates A - gt Solved: Navigating to previously activated Excel Worksheets C - gt D - gt B I would like code Solved: Navigating to previously activated Excel Worksheets that could take the user back from B - gt D - gt C - gt A The number of movements from sheet to sheet forward and backward could be quite large so I m thinking that I need code that stores the sheet names in the order in which they were activated then calls on them in reverse order I ve found code in this forum which navigates back but it goes only back one step http forums techguy org business-applications -excel-macro-previous-worksheet-easy html Any help would be much appreciated Otherwise I am forced to write code with a lot of if then statements which quickly becomes unmanageable Thanking you in advance nbsp

Relevancy 70.95%

Good evening I have just switched from Excel to where in this code was working and now it doesn t I tried to select all and to cut paste the info for a refresh but nothing Here is what I need to do I will attach my workbook In tab sheet Contracts I have information in Column A starting at row in this help Transfer code Excel Solved: with matching 2007 a case the word Testing When I insert a number in that same row but in column B it will copy the Testing from column A and add HS to column C but will also insert this Solved: Excel 2007 Transfer help with a matching code information to the next row if a higher number is inserted in Column B Example is to insert in column B It will show you a little what I am trying to do Now I had an automatic code that would bypass the rest and add the same number inserted in Column B to my next tab sheet Report to the same row matching the word from Sheet Contracts to sheet Report So if you add to Column B in the Contracts sheet it would copy the same number in Sheet Report in column L on the same row of the Testing in column A So a matching code can be found on top to the VBE in each sheet It s hard to explain but once you do a test in my Sheet Contracts you will understand a little more Now from to this code has stopped working and I cannot figure out why I need your help Please this is bugging me and I don t understand I read the code and it looks good and should function as it was intended to nbsp

A:Solved: Excel 2007 Transfer help with a matching code

Thank you but I have found my error

Sometime the only thing you need is a jolt of coffee

Thank you,
Relevancy 70.95%

I have a workbook to keep track of total and monthly sales. Worksheet 1 is Total sales, worksheets 2 - 13 are monthly sales.
If I get a new customer and want to add the record I currently have to add it to every worksheet manually.
I want to be able to add a new row to the total sales and it be automatically added to the other 12 worksheets.
I have tried selecting all worksheets and right clicking but the add new row button is faded out and won't let me click it.

A:Solved: Adding rows to multiple Excel worksheets

Worked it out. I had the data in tables. Converted back to range and it is working fine.
Relevancy 70.95%

I need to make two versions of the same pivot table. One groups something by month, one groups it by every three months. These need to be in the same worksheet.

My Month column sits in the Row grouping. When I group by 3 months in Pivot Worksheet A, it groups by three months in Worksheet B. If I then go back and ungroup Worksheet B, it goes and ungroups worksheet A. I want A to be grouped and B to be ungrouped, but they are being linked by Excel 2003.

Is there any way to get around this?

Relevancy 70.95%

I have 2 tables where the id in the main table that I need to match with the second table could appear in any one of 3 columns of the second table. I can get the data by created 2 more tables and making three separate queries. Is there any way to create a query that would merge the two tables no matter which column the matching data is in without create 3 separate tables and queries?

A:Solved: Excel Query where id matching 2nd table could be in 1 of three columns

After looking at the sql, I just added or statement for the where. I used the same syntax as matching one of the columns and then added an or and changed the column to match to.
Relevancy 70.09%

I have a large worksheet that I have a filter on so I can filter by certain columns. I make my changes and then need to print out only that filtered information.
I have most of my work done, and printed, but now I am on a worksheet where I have done the same thing from the beginning and the newly filtered data will not print like the other filtered data. It scrunches the data to the left of the page and it is so small that it cannot be read.
The theme font and size is set just like the rest are set but I cannot get it to print out correctly. (so are the margins and page set up). Everything on this filtered sheet is set up exactly like the prior sheets that printed perfectly.
Thanks for your help.

A:Solved: Excel 2007 printing filtered worksheets problem

found the answer- needed to change the pages from 1-1 to 1-3
Relevancy 69.66%

Hi There I have run into an issue with excel that s a little beyond my amateur coding skills I think this can be solved with some simple vbscript or nested excel formula All i need to happen is to compare a list of names and one additional value to another list of names and add the missing value Easier if i give an example of data the columns are as follows Column A Column B Column C Column D Name - values Excel Basic multiple Question Solved: Comparing Value Name Value Bob Test Full Bart Test Bill Test Part Bob Test Ren Test Non Bill Test Bart Test Part Ren Test So in column A and B is the reference data and you see Column C and D is where the data needs to be matched copied to The script formula needs to in this case work its way down column C - look up the matching value in column A and then copy whats in column B next to the matching value to column D Example is with the above data it would first hit in column C - Bart Test - it would then lookup cell A for Bart Test - and copy whats in column B beside it which is quot Part quot to blank cell in column D Then it would move on to Bob Test in column C - it would then lookup cell A for Bob Test - and copy whats in column B beside it which is quot Full quot to blank cell in column D I have a massive list of names that gets Solved: Basic Excel Question - Comparing multiple values emailed to in a jumbled order and may grow in cell numbers as well so i cant count on a simple A to Z sort to help out - with additional information that i need to marry up Solved: Basic Excel Question - Comparing multiple values to my existing Solved: Basic Excel Question - Comparing multiple values data I need an easy way to have it find the same name in my data and copy in the additional information provided Any ideas on a formula or vbscript that could do this - i appreciate it in advance Cheers Phenonm nbsp

A:Solved: Basic Excel Question - Comparing multiple values

This is what I think you need (See attached file) Look at the formulas in column D. The first formula can be copied down that column as many times as you require.

Relevancy 67.51%


I want to know how i can export the data from Access to excel using Access VBA for the specified sheet using data linkage with access database. Like we used to do it manually in excel as external data from access.

So that the excel size won't be that big and also it saves processing time.

Relevancy 67.08%

Hi all I m a newbie in Excel Macro or VB probably only one week into looking at this Basically I have two worksheets one for project costing budgeting and the other for quotation Now my budgeting sheet has ALL items but not necessarily all items are to be quoted thus column for the unquoted items are This is because everything in the budgeting sheet is automated based on human input in the first quot Info Sheet quot that I have also created So I wish to have a macro working to copy all the quoted items in column when column gt from worksheet quot Budgeting quot to quot Quotation quot Other than these items just do nothing This is what I have for now it s just to worksheets worksheets copying Conditional Solved: from the Solved: Conditional copying from worksheets to worksheets first half not even sure whether this is the right way Sub transfer checkkclm copyclm startrow endrow For rowcnt startrow To endrow Sheets quot Budgeting quot Activate If Cells rowcnt checkkclm Value gt Then Cells rowcnt copyclm Copy End If Next rowcnt End Sub nbsp

Relevancy 64.07%

Wonder if someone could help me please ?
I have a workbook with three tabs, these are:

Data - columns with names as headers. Each column has a postode.
UK Postcodes - a sheet containing Postcodes as a header. Each column has the individual sub codes for that Postcode.

Place - a sheet with postcodes and a unique code associated with that code.

What i would like to be able to do is the following please

1. On the output 1 sheet i would like to have the match the persons primary and secondary data with the postcodes from the UK POSTCODES tab

2. On the output 2 sheet i would like to output the matched UK POSTCODES with the code and postcode from the PLACE tab.

I have attached the workbook.

Thanks in advance


Relevancy 63.21%

I'm trying to update our inventory at work using Excel. We have the main worksheet containing all products, barcodes, stock levels, etc. for over 20,000 products, and another worksheet containing just the barcodes and our most recent stock count for roughly 15,000 products.

How do I merge the 2 worksheets so that the stock numbers match the correct barcodes?

Main worksheet columns - A: Product Name, B: Barcode, C: Current Stock Level

New worksheet columns - A: Barcode, B: Stock Count

It's probably really easy to do, but I'm new to Excel and don't know anything about formulas, merging or consolidations.

Relevancy 62.78%

I have two separate is an Inventory List, and the other is a Parts Code List. The Inventory List displays each automobile part along with a parts code. The table of parts codes and their corresponding details are found in the Parts Code List. How can I get the Inventory List to display the Parts List details on the Inventory List?

Relevancy 62.78%


I'm trying to get this to work but am having no luck. Here is the scenario.

NAME 1 ID 1 ID 2
Name 1 1
Name 2 2
Name 3 3
Name 4 4
Name 5 5

Name 1 AB0011
Name 2 AB0180
Name 3 AB0200
Name 4 AB0560
Name 5 AK0040

I need to match the NAME 1 and NAME 2 to find an exact match. If a match is made, copy ID 2 from LIST 2 to ID 2 field in LIST 1.

I've tried VLOOKUP and EXACT but can't seem to get it to work.

Any pointers to get me in the right direction would be appreciated.

A:Excel matching

vlookup should work

can you post dummy data in a spreadsheet and attach here

assuming data starts in column A2 and the list 2 is in sheet2 starting at column A2 ending in row 200 -

so in the column with ID2
=Vlookup(A2, sheet2!$A$2:$B$200,2, false)
should work, if i have the syntax right
Relevancy 62.78%

How do you paste a cell or create a link from a particular cell in one worksheet to a particular cell in another?


Relevancy 62.78%

I am new at creating macros and am running into a little problem. I'm not really sure it can be done. I know how to add sheets for daily spreadsheet for June's 30 days, but I don't want to change the date on every sheet. Is it possible to place 6/1/2009 on the first sheet and have it automatically have it add a day on the second sheet then another on the third sheet on down to 6/30/2009 on the last sheet without having to type it on each sheet?

A:Excel Worksheets

Like this? (I did this on a button click, but can be run as a macro):

Private Sub CommandButton1_Click()
Worksheets.Add After:=Sheets(Sheets.Count)
Worksheets(Sheets.Count).Name = "Sheet" & Sheets.Count
Worksheets("Sheet" & Sheets.Count).Cells(1, 1).Value = Format(Worksheets((Sheets.Count) - 1).Cells(1, 1).Value + 1, "Short Date")
End Sub
Relevancy 62.78%

Hi I have a few datasets Dataset is in the format in an excel file file below as of Mar There are main product types A B and C and each product type is categorized into products with data from January to March The headers for the product types e g type A in B to G are data to extract VBA into different codes worksheets merged cells TypeA TypeB TypeC Amount January December January February March January December January February March January December January February March Product Product Product Product Product I have issue VBA codes to extract data into different worksheets displaying table properly using Pre tag Very sorry for this You would be able to see the correct format if you VBA codes to extract data into different worksheets copy the table and paste in excel- text to column- Delimited by space I shall describe the table in details For example Product type A is displayed from Col B to G Row contained the date and row to contained the data for VBA codes to extract data into different worksheets Product to Column H to M contained data for Product type B etc As of Apr I would have another new file file and the dataset would contain data from January to April with additional columns added for the current month April data for the product types Every month there would be a new file containing additional columns for the current month I would like to have the VBA codes to extract and process the data in the following steps automatically To extract the data for certain periods only e g from January to March and paste into a new blank file in worksheet automatically Final format worksheet TypeA TypeB TypeC Amount January February March January February March January February March Product Product Product Product Product After the data are copied into worksheet the same table is duplicated in worksheet but without data Set formula such the value in worksheet is equal to the value in worksheet divided by e g Sheet B The cell references are the same for the worksheets Cells are formatted to display figures in decimal place Final format worksheet TypeA TypeB TypeC Amount January February March January February March January February March Product Product Product Product Product To duplicate worksheet in worksheet without formula copy and paste the data as values Cells are formatted to display figures in decimal place Every month there would be a new file containing additional columns for the current month I would like to have the VBA codes that could work for rest of the months and there is no need to write a new set of codes for each month Thanks for your advice and assistance in advance message edited by lhm
Relevancy 62.78%

I have different database in several worksheets. I want to use vlookup to fill in the data from different worksheets rather than combined all into one worksheet. Can it be done? Thanks.

A:How to use vlookup for data from several worksheets within the workbook?

Welcome to the board. (not tested).
Relevancy 62.78%

I have 2 separate Sheets in which Sheet 1 & Sheet 2 have separate entries.
I need Sheet 1 C column amount to be update if A column name of Sheet 1 & Sheet 2 will match & Sheet 1 B column month will match with Sheet 2 month then amount will update in Column C in Sheet1. I have attached the sheet for your reference and mark it yellow color.

A:Amount Updated after Matching Data

you can use a index/match to do what you are after
have a read here


with error

see attached
Relevancy 62.35%


I have 2 price lists from different years. I have copied and pasted the p/n's and costs from both so there is 1 spreadsheet with 4 columns a,b,c,d. Columns A and C are both p/n's from their respected years and columns b and d are costs for each of those years. I Would like to take these 4 columns and create 3. I want 1 column with all the p/ns so I would need to match and remove duplicates and then I would like to keep both of the b and d columns but have them match up properly to column a once a and c and joined and duplicates removed.

Is this possible, sorry if this is just jumbled together.

Relevancy 62.35%

Hi, everyone!
I have monthly worksheets, with a client and billable hours matrix. (no more than 20 clients, about 40 billing functions).
It was my bright idea to have a cumulative client history page, which totals up the hours per client.
But it's only April and my formula is HUGE! It covers up half of the cumulative history worksheet.
Am I doing this the right way?
I start with the cell that has Smith/Record Review, and I go:
= sum (then I click the total box for January's sheet, then February's sheet, then March's sheet, then April's sheet), and it ends up with a huge formula like 'C:My Documents\ImportantBillings\JanuaryBillings.xls then some exclamation marks, etc. And, I think I also have to change to absolute references.
It's a lot of work. I dread getting to May. Is there any easier way?
Many thanks, in advance!

A:Excel - Multiple Worksheets


Suppose you have sheets:


All worksheets are exactly the same, except the values. So, for instance, cell b5 in January may have the amount that Acme Explosives paid for fur replacement. They also paid for some of this in February, and this is in b5 of February's sheet. Now, Acme Boulders is listed in b6 of each sheet. On the summary sheet, you want to show (for now) January through April totals these guys, as well as many other expense columns and companies...

For our purpose, though, you'll type in cell b5 of the summary sheet:

Okay. So, you fill April's up, make a new sheet for May. You go to your Summary sheet, and you hit Find/Replace.

You'll make sure it's looking in *formulas*, and then put January:April in the find what box and January:May in the replace with box. Replace all.
Relevancy 62.35%

Is there a way to create and name Worksheets with dates?

In particular, July 1, 2009; July 8, 2009; July 15, 2009; etc.? for the whole year?


A:Name multiple worksheets in Excel

I don't know if you can do them all at once. You can certainly do so one at a time.In Office 2003, Right Click on the worksheet tab and choose "Rename" then type in the Date for the name.To create sheets, right click on a tab and choose Insert then Worksheet. Rename the sheets as above.Orange Blossom
Relevancy 62.35%

Hi I have data on the semiconductor industry of companies and their partners alliances for the years - and - The data is in columns in the following format Alliance ID Company Name Company ID Partner ID Partner ID Partner ID Partner ID The above is for - and - The column Alliance ID assigns a unique ID to each alliance For example if the company Intel has a partnership with other companies Partner ID and Partner ID then this alliance is given an alliance ID XXXXXX It is important to note that this ID is for a given combination of partners i e the same company Intel can have another partnership with some other company different partner ID then this Columns Matching Excel in alliance gets a different alliance ID albeit it being for the same focal company Intel However I only have alliance IDs for the - data and need to generate them for - In other words if a given alliance in - is repeated in - then I need Excel to automatically assign the - Matching Columns in Excel Alliance ID to the corresponding row in - To illustrate In - I can have the following alliance-Intel partnering with Matching Columns in Excel Kodak and Hitachi partner ID and partner ID respectively This alliance can be repeated for - Intel with Kodak and Hitachi Then I want Excel to assign the - Alliance ID to the empty cell of - Alliance ID of the SAME alliance Is there a command or macro program that can do this automatically Thanks Mithuna nbsp

A:Matching Columns in Excel

Can you please upload an example of your spreadsheet (without any sensitive data) so we can see what we are dealing with.
Relevancy 62.35%

i was wondering if there is a way to format all the sheets in a worksheet so i do not have to go to each sheet and fix it. Example: i want to show each sheet as a landscape instead of a portrait. I seem to have to go to each sheet and change it. i would love to do it for all at once.
Thank you,

A:Formatting many worksheets in Excel

Yes you do things to all sheets by selecting the tabs at the bottom of each sheet.

Select sheets
When you enter or change data, the changes affect all selected sheets. These changes may replace data on the active sheet and other selected sheets.

To select Do this
A single sheet Click the sheet tab.
If you don't see the tab you want, click the tab scrolling buttons to display the tab, and then click the tab.

Two or more adjacent sheets Click the tab for the first sheet, and then hold down SHIFT and click the tab for the last sheet.
Two or more nonadjacent sheets Click the tab for the first sheet, and then hold down CTRL and click the tabs for the other sheets.
All sheets in a workbook Right-click a sheet tab, and then click Select All Sheets on the shortcut menu.

Note If sheet tabs have been color-coded, the sheet tab name will be underlined in a user-specified color when selected. If the sheet tab is displayed with a background color, the sheet has not been selected.
You can then click on File, Page Setup... and select Landscape.

Do can do so much in group mode, but just remember that if you type something in a cell in one sheet it appears in all the sheets as well.

So make sure you turn the group mode off when finished.
To cancel a selection of multiple sheets in a workbook, click any unselected sheet.

If no unselected sheet is visible, right-click the tab of a selected sheet. Then click Ungroup Sheets on the shortcut menu.

Some of this is from the help in Excel, the rest are my comments.
Relevancy 62.35%

What is an easy way to join several Excel spreadsheets, all with the
same format? I get genetic data in Excel in three files, and I need to combine them into one. Yes, I'll have to delete the header lines, but other than that, I just want to mush them all together.

I come from a UNIX background, so what I really want is something like:

$ cp file1 all
$ sed -e "[strip header commands]" file2 >> all
$ sed -e "[strip header commands]" file3 >> all
I'd love to "push a button" and get it done, without opening up Excel, opening file1, then file2, etc.

Relevancy 62.35%

How do you add if statements to formula in Excel that change the formating of the cells.

For instace:

If I have a sum which subtracts one cell from another and I want the colour of the text in the results cell to turn red if the value becomes negative.


If I have a number in a cell and while the number is say 8 then that is fine so I want nothing to change, but if the number becomes say 3 then I want the colour of text in the cell to turn red, how would I go about it.



A:If statements in Excel worksheets.

Man, I haven't had to try this in a long while. I remember it used to be a rule menu called "conditional formatting", but I've never tried it in newer versions of excel. Give me a minute and I try to reacquaint myself.

EDIT: Home tab, Conditional formatting is it's own button
Relevancy 62.35%

I just got my new computer configured to that I can send my excel 97 worksheets to corporate. I needed to download netscape 4.5 for one of my distributors, now my excel 97 want's to send by way of netscape composer! I have everything in outlook express 6 and would like to keep it that way. Can someone tell my how to get back to my origional settings? I don't want to use navigator composer and all of my stuff is in outlook.


A:E-MAILING excel worksheets

right click your IE and go to properties then the programs tab and you will want to change your e-mail back to outlook express.
Relevancy 62.35%

Hi Everyone,

In Excel, if I have a worksheet for each week, what is the best way to keep a running year-to-date average of a value that is in each sheet?

More detail: Each day I am tracking the hours spent on work orders and total hours on the clock. At the end of the week I calculate a "documented hours" percentage. (sum of work order ours/sum of total hours). I want to compare this week with previous weeks and keep a running ytd percentage.

I appreciate any help you can provide!

Relevancy 62.35%

I would like to convert an excel file with multiple worksheets into a single pdf file. I've tried to do it by using adobe pdf as the printer and checked the print to file box. When the file is created - even if I've attached a pdf extension when naming it - when I try to open it with acrobat, I get an error message that the file is not supported under adobe. is there another way to create a single pdf form form multiple worksheets without doing them one at a time?


A:pdf from multiple excel worksheets

simple solution. Use openoffice.
Relevancy 62.35%

Can you take the option for adding a new sheet to a workbook away?

A:Locking excel worksheets

If you are using Excel 2007 go to The Office Orb>Excel Options>Popular. Go to When creating new workbooks and change the setting. In Excel 2003 go to Tools>General tab
Relevancy 62.35%

I have an excel file that contains several fields, of those fields
I have 2 fields that match and a dr and cr that offsets. I am trying
to create a macro or anything that will work that will take those
matches and store them on separate spreadsheet and leave the records
that have no matching data.

Any help would be appreciated


A:Excel auto matching

All the match will do is return the location of the match from the array or range of cells. You might be able to do this conditionally.
Relevancy 62.35%

I have two Excel worksheets containing data. I want to know if there is a way of comparing the two to find which rows from worksheet 1 exist in worksheet 2.

Cheers in advance for any help.


A:Excel - Compare worksheets

This may be useful to you - it compares each cell between two work sheets- worked brilliantly when I used it for a weekly European Sales meeting
Relevancy 62.35%

combining worksheets into one excel combining in worksheets sheet i have a number of worksheets which i want to combine into one standard worksheet each worksheet is formated the same rows - are hidden - and contain data for dropdowns etc row header row row onwards data the header combining worksheets in excel and data are the same for all sheets and same number of columns A S however Not all columns have to contain data I have copied this macro off the web but it does not quite work correct it copies Row and Row for all sheets I expected the header to NOT be copied help what am i doing wrong please Sub CombineSheets test Dim J As Integer On Error Resume Next Sheets Select Worksheets Add add a sheet in first place Sheets Name quot Combined quot copy headings Sheets Activate Range quot A quot EntireRow Select Selection Copy Destination Sheets Range quot A quot work through sheets For J To Sheets Count from sheet to last sheet Sheets J Activate make the sheet active Range quot A quot Select Selection CurrentRegion Select select all cells in this sheets select all lines except title Selection Offset Resize Selection Rows Count - Select copy cells selected in the new sheet on last line Selection Copy Destination Sheets Range quot A quot End xlUp Next End Sub Click to expand nbsp

Relevancy 62.35%

This should be a piece of cake, I just know it! But me and excel never see eye to eye. I have a simple document with column a and column b. I want to find the matching ones and create a column C with the ones that are not matched. Can I do that? Without using copy paste for three hours?? I attached the file below.


Relevancy 61.92%

Real dummy here using Access. I have two tables containing 80,000 names. These tables are going to change weekly. What I need is a query that will compare new user in table 2 to the ones in table 1. Then I need a query that will show the users that are not in table 2 that were in table 1.Hope this makes sense.

Relevancy 61.92%

Hello I have two problems I have a lot of scanning data all date and time stamped All the scans show different status of shipped packages all shipments have unique ID but there are multiple scans data export from macro to second Solved: excel ACCESS: and Show latest data on route for all Solved: ACCESS: Show second latest data and export data to excel from macro of them to track the packages I want to find all the packages for which there was quot misdirected quot scan then for all of them show the scan before So the end result would be all the scans that show for each package where it was misdirected the terminal of the scan before the misdirect scan I want to automate this run queries every two hours between am and pm copy and paste all results into Excel to specified areas e g first query results to A second to A third to A etc or onto different tabs save as csv to a specific folder then send it as attachment to a given e-mail address Even partial solution can be good I only know macros somewhat not as much VBA so I tried that with the TransferSpreadsheet command but can t get it work Any help is greatly appreciated nbsp

A:Solved: ACCESS: Show second latest data and export data to excel from macro
Relevancy 61.92%

Excel 2003 SP3
Could anybody suggest a way, using whatever method you prefer to copy existing data to another tab, but have the data go transparent by 50%(or thereabouts, so you can just see it), but keep the headings normal font.

A:Solved: Excel 2003 help to copy data to new tab and make data transparent
Relevancy 61.92%

I have two spreadsheets:

One has rows with the invoice numbers and date and sales number etc for only one state

the other has rows with invoice numbers and dates and sales data etc for ALL state.

I would to match the info with first spreadsheet with the data of the second spreadsheet
using the invoice numbers, that are the same in both, as a key match .

How would I go about this?

A simple Example to illustrate:

Sheet 1 ------------------ Sheet 2
A --------- B ---------------- A ------ B
X --------- 500 ---------------- Y ------- 540
Y -------- 1000 ---------------- Z ------- 100
Z -------- 200 ----------------- X ------- 800

And I need the result as this :
A -------- B --------.... C
X -------- 500 -------- 800
Y -------- 1000 -------- 540

Relevancy 61.92%

Hi I have Worksheets I would like to compare the columns and add missing employees to Different Worksheets Columns Add Data on and Missing Compare the main worksheet and highlight any employees that no longer on the nd sheet Sample - Sheet Plant - PA's Column A Compare Columns on Different Worksheets and Add Missing Data Column B Column C Row Employee Name Employee ID Start Date Row Employee VLOOKUP Formula to Sheet VLOOKUP Formula to Sheet Row Employee Compare Columns on Different Worksheets and Add Missing Data VLOOKUP Formula to Sheet VLOOKUP Formula to Sheet Row Employee VLOOKUP Formula to Sheet VLOOKUP Formula to Sheet Row Employee VLOOKUP Formula to Sheet VLOOKUP Formula to Sheet Sample - Sheet Employee List Query Column Compare Columns on Different Worksheets and Add Missing Data A Column B Column C Row Employee Name Employee ID Start Date Row Employee Row Employee Row Employee Row Employee In the above example I would like to be able to add Employee from the Employee List Query to the bottom of the Plant - PA's sheet along with the VLOOKUP formulas in Columns B C I would also like to highlight Employee on the Plant - PA's as it has been removed form the Employee List Query sheet Hopefully this makes sense and is possible Thank you Sandi

A:Compare Columns on Different Worksheets and Add Missing Data

Hi,Here is an example of how i would do it, usually others will have a much cleaner and probably better way of coding the solution, however this is my way of doing it.Hopefully DerbyDad can provide a solution too.There are two method which you will need to call1) CopyMissingEmployeeThis will call IsOnOtherList and check if the employee exists on the other list, if not then the entire row is copied to sheet 12) HiglightRemovedThis will call IsMissing and will highlight any employees on sheet 1 where they do not appear on sheet 2.Hope this helpsPrivate Sub CopyMissingEmployee()

Dim URange, LRange
Dim BCell As Range
Dim NextRow

Set URange = Sheet2.Range("A2")
Set LRange = Sheet2.Range("A" & Rows.Count).End(xlUp)

For Each BCell In Sheet2.Range(URange, LRange)

If IsOnOtherList(BCell.Value) = False Then


NextRow = Sheet1.Range("A" & Rows.Count).End(xlUp).Row

Sheet1.Paste Sheet1.Range("A" & NextRow + 1)

End If

Next BCell

End Sub

Private Function IsOnOtherList(EmpID As String) As Boolean

Dim URange, LRange
Dim BCell As Range

Set URange = Sheet1.Range("A2")
Set LRange = Sheet1.Range("A" & Rows.Count).End(xlUp)

For Each BCell In Sheet1.Range(URange, LRange)

If BCell.Value = EmpID Then
IsOnOtherList = True
Exit For
IsOnOtherList = False
End If

Next BCell

End Function

Private Sub HiglightRemoved()

Dim URange, LRange
Dim BCell As Range
Dim NextRow

Set URange = Sheet1.Range("A2")
Set LRange = Sheet1.Range("A" & Rows.Count).End(xlUp)

For Each BCell In Sheet1.Range(URange, LRange)

If IsMissing(BCell.Value) = True Then

'BCell.EntireColumn.Interior.Color = vbYellow
BCell.EntireRow.Interior.Color = vbYellow

End If

Next BCell

End Sub

Private Function IsMissing(EmpID As String) As Boolean

Dim URange, LRange
Dim BCell As Range

Set URange = Sheet2.Range("A2")
Set LRange = Sheet2.Range("A" & Rows.Count).End(xlUp)

For Each BCell In Sheet2.Range(URange, LRange)

If BCell.Value <> EmpID Then
IsMissing = True
IsMissing = False
Exit For
End If

Next BCell

End Function
Relevancy 61.92%

I frequently need to export data to Excel fom the same source. One report may export , for example, customer name, address, City State Zip. The other may have customer name, year they became customer and SIC code.

I need a macro code that will take the data on the customer in worksheet 1 and add it to the same row that has the same customer name in worksheet 2, so that all the data (or the selected data) for the same customer is all on the same row in worksheet 2.

The two reports will have multiple customer names.

Is there some simple macro, code I can insert into my macro to do this?

I'm using Excel 2003


Relevancy 61.92%

Hi, I have 2 tables

-New Data

What i need to do is match all the data in the "New Data" table to the data in the "Masterfile" if a match is found then do not append that record in to the masterfile. Is this possible using queries?

Relevancy 61.92%

When i export my inventory from my DB into an excel file my trailer brands are listed as they are in my DB of course But when i bulk upload them with XML feed to trailer inventory sites HTW com amp HTT com for our example here My inventory is listed on their site but it s not searchable because the brand search is a Replace with similar - sample data required Solved: Formula? Excel my data or -Macro drop down Solved: Excel -Macro or Formula? Replace my data with similar required data - sample menu on these sites Say i goto HTW com and do a search for Blue Ribbon trailers none of my blue ribbon trailers show up because all of Solved: Excel -Macro or Formula? Replace my data with similar required data - sample mine are listed as BLUE RIBBON TRAILERS LTD and the site is searching for Blue Ribbon and although they are the same trailer brand HTW com does not see them What i m looking for is maybe a Macro or even formula that once i tell it what site i m working on will go through my list and replace all my spellings with the proper ones for that site Attached is an XLS file with columns the first is my list as is from my DB the other columns are the proper spelling for each corresponding site Excel s find and replace feature is a VERY crude fix to this but since it s so time consuming it s NOT a viable solution Any takers Slurpee Zack Anyone nbsp

A:Solved: Excel -Macro or Formula? Replace my data with similar required data - sample

for the record, a complete solution would be GREAT, but not necessary...
I just need someone to point me in the right direction and maybe a small working sample of like 5 brands and i'll expand the list from there...

Relevancy 61.49%


This may be a very elementary question. I am not a super sophisticated excel user, but was wondering if there is a way in Excel to compare the formulas (instead of the results) across several worksheets in a workbook. For example, I need to know if the formula in cell A1 on every worksheet is the same.

Optimally, if one does not equal the test formula, I would like to have the tab name returned.

Thanks in advance,

Relevancy 61.49%

Hi, I have 2 lists of numbers in excel, both lists are about 40000 long each. I need to see how many records match and see what ones are unmatched?

Does anyone know of an easy way to do this? Have tried 'IF' but could only make it check individual cells rather than the whole list?


A:Comparing lists in excel?

I think you might find Steps: 4 & 5 useful in the following article - Use Excel to compare two lists of data - Excel - Microsoft Office
Relevancy 61.49%

Hi Computer Whizzkids!
I am scratching my head trying to work out how to do the following:-

I want to be able to type an alphanumeric 'stock code' into an excel spreadsheet (E.G. "C000P8VWCW"), and have it automatically bring up the relevant 'Product Name' in the column next to it upon hitting the return key (E.G. 'PACK OF 10 HIGHLIGHTER PENS').

I know it is possible as an old work colleague did it a few years ago. I think he had a product list in a separate worksheet, that somehow linked into the stock codes. I cant figure out how he did it though.

Help would be much appreciated.

Note: I am using vista, with office 2007.
Apologies in advance for being a dumb a**!

A:Making Different Excel Worksheets Communicate

Hi Roger and welcome to the forum!Im thinking that you want to use the VLOOKUP function for thisSee if this helps
Relevancy 61.49%

I am converting an Excel 2007 workbook with approx. 100 worksheets to a PDF. Each worksheet is named to generate an appropriate bookmark in the PDF when converted.

The converion runs fine without error, however the bookmarks in the PDF do not link to the appropriate page.

The error seems to be related to the page layout in excel. All worksheets are set to fit to 1 page...after undoing this option to a few of the worksheets that were not linking correctly, I converted and all bookmarks linked correctly. This doesn't solve the problem because all the data on each worksheet needs to be on one page.

Any ideas why this is occuring?

A:Acrobat bookmarks via Excel worksheets

I haven't found the root of the problem, but did run across a workaround. I set all pages to be 70% of their normal size...did the conversion and all bookmarks link correctly. This does make some pages smaller than they have to be, but it's better than going in and manually setting all the bookmarks.
Relevancy 61.49%


I'm trying to make a summary worksheet that is based on cells from other worksheets within an Excel 2003 file. The formulas work until I update the data in the file using a macro and then I get #REF! errors. Seems the formula loses the worksheet part of the formula.

For example, the formula for cell A2 on Sheet 1 is ='Sheet2'!C4. Before I run the macro, cell A2 shows the number from Sheet 2, cell C4. After the macro, the fomula becomes =#REF!C4. It's losing its link to the other worksheet.

Any suggestions here?



Relevancy 61.49%

I can open worksheets only from within Excel using the File, Open command. Trying to open from My Documents brings up an error box reading 'Windows cannot find (correct pathname shown), you may have typed the name incorrectly in the Run dialog, or another open program cannot find a system file....', which is silly as I am only clicking a file name, not typing anything!
In Folder Options, File Types .xls is correctly associated with Excel and the files are not Read Only, and it shouldn't matter if they were.
From this I'm not sure if this is a Windows (ME) error or an Excel error. MS Word opens docs from My Computer without any problem.
I have used the repair function with the Office disk in Add/Remove but no change.
Has anyone experienced this and solved it?


Relevancy 61.49%

OK. I know this may have been covered in another post but here is what I've got. I generated two reports from my system that have results for year 2010 and YTD 2011. The columns are all the same. I want to combine the two worksheets to show comparison between the two years. The two criteria to break down the results are Name and Product. There will be many of these that are common between the two worksheets so I just want to combine those and show the 2010 to 2011 comparison. And I want any unique Name/Products listed also where there might only be that Name/Product in either only 2010 or 2011. I have attached a sample. Thanks in advance for any help!

A:Compare Excel worksheets 2010

This is pretty easy. All you need to do is copy the data from 1 sheet to another and make a pivot table on the data. You can probably use the wizard to figure out how to set up the table up to display what you are after... but if you need help, do a simple google search to understand pivot tables better.... If you are still stuck, please reach out to me
Relevancy 61.49%

Hey -

How do I set different (scaling) print percentages for each worksheet within an excel workbook?

For example: My workbook has 5 worksheets and I would like for 4 of the 5 pages to print at 75% scaling and the 5th page to print at 68% scaling.

Thanks in advance

A:Excel: Print % different for worksheets within workbook

From each worksheet, go to File-Page setup, and set the percentage. You can group worksheets, tho I'm not sure you can set the page setup when they are grouped, but I would try it. Click on worksheet 1 tab, hold shift, then click on worksheet 4 tab. Do the page setup, click on worksheet 5, do page setup. Then they should all be set as you desire them.
Relevancy 61.49%

I have 2 reports that I want to merge which are exported from different applications.

The problem is that the names appear in a different format and I can't work out how to match them up.

The fact that not everyone appears on every report I have solved by using lookup as in the attached workbook.

What I need to do is use a formula that will recognise the two different name formats and match them up.

i.e. Campion, Ben P. and Ben Campion.

Please see attachment.


Relevancy 61.49%

I'm written a complex application using VBA in Excel and I need to make sure that it's protected. I writtten the code so that when the document opens the entire workbook is protected. I've created a toolbar with butttons that access individual user forms with various data input areas. What I need to do is have document unprotected when the user clicks on the toolbar button and then reprotected when the user either clicks the "OK" button OR clicks the "X" in the corner of the window to close the userform for each of the five buttons I've created. Any suggestions as to how to do this?


Relevancy 61.49%

Hello I have been struggling to figure out a formula formulas that will Excel and Formula(s) Mapping Matching help me match and map a set of categories to another set of categories Bascially what I am trying to do is match google s defined categories Excel Matching and Mapping Formula(s) to my own ecommerce categories based on a loose match then return my categoryID Ultimately this will help save me some time categorizing products as oppose to the tedious venture of it doing all of the manually Attached is the sheet I am working with In the first columns are Google s cat hierarchy Column J is my formula which I created using the help of a previous post ISTEXT INDEX W AD MATCH quot quot amp F amp quot quot Excel Matching and Mapping Formula(s) W AD I have it looking at the lowest level of the category hierarchy looking for a match in my lowest level then if no match go on the to next level up and so on I am having luck with some matches however I am having something like a hit rate which isn t doing me much good Now I know I won t have and I know some matches may give back an incorrect match i e bicycle recycle due to the wildcard inputs however any amount greater than would save me a ton of time Ultimately I d like to take this a step further and return the CategoryID Col V associated with the given match from my store If anyone is out there that could take a look or offer some suggestions solutions help I d greatly appreciate it Thank Excel Matching and Mapping Formula(s) you Matt nbsp