Windows Support Forum

Need Excel 2007 help on comparing cells

Q: Need Excel 2007 help on comparing cells

Good day -

I need to compare cells in the same column in a BIG Excel 2007 file. When a change in the values is found, insert a row. Example:

A801
A801
A801
999B
999B
80C5
80C5

Using this example, a blank row would be added between the last A801 entry and the first 999B entry. And another blank row would be entered between the last 999B entry and the first 80C5 entry. I.e.,

A801
A801
A801

999B
999B

80C5
80C5

As you can tell from my example, the cells to be compared are alpha/numeric. Ideas?

Thank you in advance. abgbpb

Relevancy 100%
Preferred Solution: Need Excel 2007 help on comparing cells

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 http://downloadreimage.com/directdownload.php. (This link will automatically start a download of Reimage that you can save to your computer.)

Relevancy 95.46%

Hello!
I want to compare two cells in excel. Each cell is multi-valued. i want to compare both cells and place common number and uncommon number in two different cells.
i.e.
Col A Col B Col C Col D
1,2,5,7 4,5,7,2
2,3,1,8 3,5,9,1
3,5,9,6 4,9,8,6

So i want to compare each cell in A & B and put common numbers in C and different numbers in D.
Can i do it with formula? bcz i have a very large list..
please help me.
Thanks
 

A:comparing multivalued excel cells

Welcome to TSG!

I'd click on "Report" below your first post here and request thread moved to "Business Applications" forum.
 

https://forums.techguy.org/threads/comparing-multivalued-excel-cells.857786/
Relevancy 82.99%

Hey guys, I have a spredsheet with 3 seperate worksheets. One has a huge list of properties in a column, and the second worksheet has another huge list of properties in a column. I need to know how to compare column 1 (on worksheet 1) with column 2 (on worksheet 2) and then return values that have close or exact match.

E.g

Table 1 Table 2

Sky Hotel Moon Hotel
Golden palace hotel Golden palace hotel
Atrium Hotel Atrium Hotel
Beach Hotel Beach hotel

I need these values to be compared, and ones that match/or do not match to be listed on worksheet 3. E.G

Worksheet3:

Table 1 Table 2
Golden Palace Golden Palace
Atrium Hotel Atrium Hotel
Beach Hotel Beach Hotel

(notice how sky hotel has not been displayed here)

If Vlookup or something similar is the formulae to use, could you tell me how to use it, as I've tried but to no result.

Thanks guys!
 

A:Excel 2007 - comparing values in one worksheet to another

The simple answer is arrange the tables next to each other (1 in column A, 2 in column B), then use:

=A1=B1

in column C to return TRUE or FALSE.

However, I suspect it's more complicated than you're suggesting.
 

https://forums.techguy.org/threads/excel-2007-comparing-values-in-one-worksheet-to-another.697896/
Relevancy 82.99%

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.

Thanks
Kapil
 

Relevancy 81.27%

I have a file with a worksheet containing an Excel Table in which data will be - 2007 locking cells Excel Table Excel an in pasted into say columns A thru W Columns X thru Z contain formulae I want to be able to protect these formula columns so they are locked yet will still expand autofill when new data is pasted into columns A - W I have successfully done this in one Excel file but now I come to do it again I find that the data will not paste in if the worksheet is protected It looks like it s trying to paste in as the paste range gets highlighted but no data is visible If I do the same thing with the sheet unprotected the data pastes in no probs and the formulae autofills I can t for the life of me work out how I did it last time around and all advice I can find on the internet says it s not possible yet it clearly is cos I ve done it once In Excel 2007 - locking cells in an Excel Table both instances the working file and the new non-working file I first Excel 2007 - locking cells in an Excel Table unlocked all cells then locked cells W Z formula column titles and first line of formulae and then protected the worksheet I guess I must have done something else with the original file but I don t know what Any ideas nbsp

Relevancy 79.12%

I have a spreadsheet made in excel 2007. In one of the cells is a number with three zeros in front of it and one zero at the end. However it always drops the zeros and shows only the other numbers. I select the cell and click on "format cells", but there is nothing I see that will show all the zeros. It seems a little dumb--but maybe it's just me. isn't there a way to show everything?

A:Format cells in excel 2007

  
Quote: Originally Posted by Frank1


I have a spreadsheet made in excel 2007. In one of the cells is a number with three zeros in front of it and one zero at the end. However it always drops the zeros and shows only the other numbers. I select the cell and click on "format cells", but there is nothing I see that will show all the zeros. It seems a little dumb--but maybe it's just me. isn't there a way to show everything?


It drops zero's after comma so 3,53000 is displayed as 3,53? You want a fixed number of decimals?

00065712 is displayed as 65712? You want a fixed number of characters in front of the decimal comma?

http://www.sevenforums.com/microsoft-office/317521-format-cells-excel-2007-a.html
Relevancy 79.12%

Can anyone help me figure out how to do the following?

Cell A1 has a date of 12/15/12 and cell B1 has a date of 1/15/13 I want it to color cell B1 red. If A1 has a date of 12/15/12 and B1 has a date of 12/15/12 then I want it yellow. If A1 has a date 12/15/12 and B1 has a date of 12/1/12 then I want it green. Help!
 

A:Coloring Cells in Excel 2007

use conditional formatting and set up some rules

use a formula

=AND(A1= DATE(2012, 12, 15), B1=DATE(2013,1,15))
format red

=AND(A1= DATE(2012, 12, 15), B1=DATE(2012, 12, 15))
format yellow

=AND(A1= DATE(2012, 12, 15), B1=DATE(2012, 12, 1))
format green

see attached
 

https://forums.techguy.org/threads/coloring-cells-in-excel-2007.1083559/
Relevancy 79.12%

I am having problems with Excel 2007 cells not refreshing. When I am updating a spreadsheet, the cell that has the formula will not refresh. I have to click on the cell and hit tab each time for the updated calculation. The same problem occurs when I am using the autofill function. Please help. Thanks!

**Update - I figured out how to correct the autofill error, but I still need to know why my cells containing formulas will not refresh when I update my spreadsheets. Thanks!**
 

Relevancy 79.12%

Hello,

Having a lot of trouble with formulas this morning.

Yes, automatic calculation is enabled.

I'm having to go into each individual cell and click and hit enter to get them to update. It will update with the correct value at that point, but if I do it one more time, it goes back to displaying 0.

So odd, and I have no idea what to do.

Thanks!
 

A:Cells won't refresh Excel 2007

what happens if you press F9 ?
 

https://forums.techguy.org/threads/cells-wont-refresh-excel-2007.1020800/
Relevancy 79.12%

I'm trying to format a column of cells to show all digits.
For example, I have 007435, but Excel keeps on changing it back to 7435.
How do I make it so that it shows all the digits??? I've tried formatting to all different types, but none seem to work...
Any help would be appreciated
 

A:Formatting cells in Excel 2007

To get Excel to recognize leading zeros, you have to put an apostrophe before the number. So instead of 007435, you can enter '007435. Hope that helps.
 

https://forums.techguy.org/threads/formatting-cells-in-excel-2007.982532/
Relevancy 79.12%

I need to format the cell to include a certain year when I insert the day and the month. By default, the year is 2008. I need to change this to 1999.
 

A:Excel 2007 - formatting cells

Could you give us a little more explanation? Why can you not just type the month, day, and year into the cell?
 

https://forums.techguy.org/threads/excel-2007-formatting-cells.778617/
Relevancy 78.26%

Have several Excel docs that have only 2 short rows of data, but blank cells all the way to cell IV60927, which makes the document huge. Also gets quarantined everytime we try to email them. Is there a way to "delete" the empty cells to reduce the size of the spreadsheet? I've tried selecting the rows and deleting, but Ctrl+End stills takes me to IV60927.
 

A:Excel 2007 blank cells issue

Hi there!

Take a look here...

http://www.vbaexpress.com/kb/getarticle.php?kb_id=83

That code was actually designed for Excel 2003, but you can use in 2007. I would make one adjustment though.

Change these two lines...

Code:
.Range(Cells(1, LastCol + 1).Address & ":IV65536").Delete
.Range(Cells(LastRow + 1, 1).Address & ":IV65536").Delete
... to this...

Code:
.Range(.Cells(1, LastCol + 1, .Cells(.Rows.Count, .Columns.Count)).Delete
.Range(.Cells(LastRow + 1, 1, .Cells(.Rows.Count, .Columns.Count)).Delete
Run that and see if it helps.
 

https://forums.techguy.org/threads/excel-2007-blank-cells-issue.812671/
Relevancy 78.26%

I have a spreadsheet which formally worked in previouys versions of Excel.

The problem is that when I sort the table of data it mixes the lines up, although the data still references back to the correct cells. In previous versions of excel the lines of data remained aligned..

I sort on 3 levels of data which counts numbers of Urgent, medium, low impact incidents on a monthly basis. The data is colated in this table and then graphed to see our worst performing services.

When I copy the data and paste special (values) back into the spreadsheet the sort will work as expected.

Can anyone assist
 

A:Excel 2007 Sorting cells containing Formula

can you post a sample spreadsheet
 

https://forums.techguy.org/threads/excel-2007-sorting-cells-containing-formula.910868/
Relevancy 78.26%

A problem that has only just started An Excel spreadsheet set up to explore automatically cells refreshing not Excel 2007 different scenarios has been working fine but some cells not all have suddenly stopped authomatically refreshing after a value has been changed eg Excel 2007 cells not automatically refreshing the sheet calculates a number of fees based on a certain system size Change the system size and some cells update others do not and only update after F is pressed quot Automatic quot is turned on under workbook calculation I have checked other spreadsheets open at the time and Excel 2007 cells not automatically refreshing all have automatic turned on The formulae involved are mostly IF statements calling up other cell references depending on the IF conditions - most of which relate to system size I have tried changing formula even putting the formula somewhere else and then doing a direct reference to that cell No change in the way the final cell behaves - still does not auto refresh Very annoying and likely to generate expensive errors if someone forgets to manually refresh via F nbsp

https://forums.techguy.org/threads/excel-2007-cells-not-automatically-refreshing.1068921/
Relevancy 78.26%

Hi,

I use an Excel spreadsheet for my work which I constantly update, the problem is when I add new data I want it to auto arange into alphabetical or numerical value order without me having to highlight cells and use the A-Z feature. Is there any way to have Excel 2007 auto arrange in this way? I am not sure it can even be done but I think it should be a feature of Excel if it cannot.

Thanks in advance!
 

A:Can you auto arrange cells in Excel 2007?

U could use a VBA code (worksheet change) to do this!
But we need to know more detailes, like: is it always the same column, the number of columns u need to be included in the sort operation, does this range change or not and so on!
A sample file will help as to understand your question beterr too!
 

https://forums.techguy.org/threads/can-you-auto-arrange-cells-in-excel-2007.672005/
Relevancy 78.26%

I have copied Comments to their own cells in my Excel file using vba. The Comments in the new cells do not seem to actually contain the value that they are showing. The Comment "appears" in the cell but the value really isn't there in the formula bar.

I eventually need to save the Excel file as CSV to import elsewhere and I need the Comments to be there own separate field that I can import.
 

http://www.pcreview.co.uk/threads/excel-2007-copy-comments-to-cells.4062101/
Relevancy 78.26%

I have a spreadsheet with some blank rows. I want to move up the rows below so as to eliminate those blank rows. Most cells on that spreadsheet have formulas in them.

I can move a row by cutting and pasting, and the formulas adjust. But elsewhere on the sheet, I get #REF! in cells which depend on formulas outside the area.

I had LOTUS before, and there I could simply click /m, and that would move any area I had highlighted to wherever I designed it to go - with no detrimental effect on any other cells with formulas.

So how can I do that in Excel 2007?
 

Relevancy 78.26%

Right now I have code behind my Excel 2007 workbook that will either protect or unprotect my workbook.

Is there a way to modify the code to only unprotect the cells that do not contain a formula?

Here is the code I have now
Code:

Private Sub cmdOk_Click()

Application.ScreenUpdating = False

With ThisWorkbook.Sheets(1)

If Password = txtPassword.Text Then
.Unprotect sPassword
Range("A2").Select
Unload Me
Else
MsgBox "Incorrect Password Entered", vbOKOnly, "INVALID ENTRY"
Me.txtPassword.SetFocus
Me.txtPassword.Value = ""
Exit Sub
End If
End With

Application.ScreenUpdating = True

End Sub
 

Relevancy 77.4%

I want to use absolute reference for coding in my excel vba for example:
ActiveCell.FormulaR1C1 = _
"=SLOPE('Second Reaction Data'!I4:I61,'Second Reaction Data'!D461)"
I want it this way but it is not working.

It works if it is typed as follows:
ActiveCell.FormulaR1C1 = _
"=SLOPE('Second Reaction Data'!R[-124]C[7]:R[-67]C[7],'Second Reaction Data'!R[-124]C[2]:R[-67]C[2])"

Can someone help how to run this formula using absolute cell referencing ex: A1:A4 instead of RC
 

A:How to code in excel vba 2007 using absolute refernce for cells

Hi srinlvashydmu, Welcome to PCR, I am afraid I do not have not the answer to your question but I am sure someone else will point you in the right direction. Once again welcome to our site and please participate and nice to have you aboard.
 

http://www.pcreview.co.uk/threads/how-to-code-in-excel-vba-2007-using-absolute-refernce-for-cells.4070823/
Relevancy 77.4%

I have an ongoing issue various spreadsheets none in particular could be one or two or all of them will start taking on this 2007 cells Excel Microsoft highlights multiple behavior for no apparent reason when you click an individual cell it highlights that cell plus usually two Microsoft Excel 2007 highlights multiple cells to the right of it Reformatting by using Microsoft Excel 2007 highlights multiple cells the format painter from an unaffected cell if you can find one at that point that hasn t been affected usually corrects it but it s time-consuming to manually go back and reformat each affected cell What causes this behavior and how can I stop it Or is there an easier fix for it TIA Have found related posts Microsoft but MS apparently has not yet acknowledged that this is in fact an quot issue quot http www microsoft com office com en-us-excel amp lang en amp cr US amp sloc en-us amp m amp p http www microsoft com office com en-us-excel amp lang en amp cr US amp sloc en-us amp m amp p My suggested workarounds are as follows Try tapping the wheel of scroll mouse if applicable Try changing the page view from Page Layout to any other View option View tab gt Normal Close the spreadsheet open again and see if behavior still exists Close Excel open the spreadsheet and see if behavior still exists If cell still selects multiple cells up keyboard up down left right arrows to navigate away and then back to cell then click in the formula bar to edit If it says EXT or Extend Selection in the bottom left corner of Excel then it is in Extended mode - try tapping the F key once Try zooming out then clicking the cell selection again View tab gt Zoom gt Edit in Custom field gt Click OK nbsp

https://forums.techguy.org/threads/microsoft-excel-2007-highlights-multiple-cells.739022/
Relevancy 77.4%

Most windows programs, selected objects color is based on the theme the computer is using. Changing the select highlight color would fix this. Excel ignores this particular theme rule. When have a set of selected cells, there is little visual difference between selected and non selected cells. The only difference that i can tell, is a slightly bolder grid line around selected cells and slight decrease in background color sometimes. This very easy to over look and may cause problems when sorting your spreadsheet, because you didn't realize you haven't selected the desired amount of cells. I have this problem all the time and I am surprised Microsoft hasn't patch this by now. Is there a way to fix this?

 

https://forums.techguy.org/threads/how-can-you-change-the-color-of-selected-cells-in-excel-2007.1155265/
Relevancy 77.4%

I have a worksheet open in Excel 2007 and I want to edit several cells at the same time. I hold down my CTRL kb button and click on multiple cells. As I click on the cells, the background color of these cells temporarily changes color so that you can easily see which cells you have selected. I would like to change that color but cannot find where I do that. I think I have checked all the tabs and option screens, colors and themes but I'm sure that I have missed it. Can someone please help me do this? Thanks.

A:Excel 2007 change color of selected cells

Is it the same as excel 2003 you select the cells then right click then select format cells , goto the patterns tab and select the color you want?

http://www.techsupportforum.com/forums/f57/excel-2007-change-color-of-selected-cells-221317.html
Relevancy 77.4%

I'm working in EXCEL 2007 and need to copy and paste some text into a WORD document using the copy and paste feature only once. Column A has 100 rows and each row has a single cell containing one name. Example of Column A seen below:
John Smith
Mary Jones
Jim Doe
Kathy Walters
etc...

Is there a concatenate formula to combine each name into a single cell without having to type fx =concatenate( a5,a6,a7,a8,a9,a10,etc.....) one hundred times? When I try to use this formula, it won't let me combine more than 30 cells of names.

Thanks in advance for your help.

 

A:EXCEL 2007 Combine multiple text cells

Don't know if this helps?
If you copy the column and 'paste special' then select transpose it will paste the column values into a row.
Don't know (seem I don't know much?) But the Transpose and Index functions may provide what your looking for.

Found this on Yahoo . A promising one time fix solution.

Example of Transpose in a formula - Tech Republic
 

https://forums.techguy.org/threads/excel-2007-combine-multiple-text-cells.1101757/
Relevancy 77.4%

Just wondering if there was a way to link cells in excel 2007, that would also link the formats. we change the precident cell to a different colour background to represent a different date, so making the dependant with it would be great.

I am also wondering what the best way to link a cell would be? the "paste link" function works well, but if i do more than one cell at a time, the dollars signs do not get pasted. I've tried typing "=" and then clicking on the cell. I think the main difference is the dollar signs, but just wondering what the positives/negatives of doing it either way would be, and what is the best way to link?

Thanks!
 

https://forums.techguy.org/threads/linking-cells-in-excel-2007-including-formats.744946/
Relevancy 76.54%

Hi,

I have set conditional formatting to colour a cell if a corresponding cell is populated.
This works fine.
When I try and copy the formatting to other cells it does not use relative referencing but continues
to referene the first cell.
I have tried selecting the different 'copy' options to no avail.
can anybody explain what to do, please?
please see attached spreadsheet. I am using excel 2007.

Thank you..
 

A:Solved: Excel 2007 - conditional formatting copy to cells?!

Where you have

=NOT(ISBLANK($G$8))

remove the $

so it reads

=NOT(ISBLANK(G8))

press apply, press ok

and now when you drag this down it will work for all adjascent cells
 

https://forums.techguy.org/threads/solved-excel-2007-conditional-formatting-copy-to-cells.1001144/
Relevancy 76.54%

Is there a way to protect a cell so that its content cannot be changed/moved.

Also, for the same cell, which has a formula, is there a way to hide the formula?

Thanks
 

A:Solved: Excel 2007 - Hide formula, protect cells

Select the cell (cells) you wanna protect, rgiht click, Format cells, go to the protection tab, and check both looked and hidded, click OK
After that go to Review Tab, and select Protect Sheet, Type a password if you wanna and save file
Close and reopen it!
 

https://forums.techguy.org/threads/solved-excel-2007-hide-formula-protect-cells.741974/
Relevancy 76.54%

The problem is I can't select multiple cells with control clicking them. I was wondering if Excel is glitching up, if I should reset its settings (how can I?) or what can I do?

Edit: It seems that this problem is also affecting Word, so I assume it's a problem with Office.
 

Relevancy 76.54%

Hi everyone I m new to this forum I have fairly good Excel knowledge in terms of formulas etc but only very basic knowledge in VBA This problem has got me stumped Background My company has about maintenance workers who go out and check our assets located throughout our region Each maintenance worker scans the asset codes he attends to and his PDA gathers all the scans and emails all of his jobs completed as an email at the end of his day The email body contains csv file containing asset number address date time etc fields There are altogether about fields columns and he attends about - locations each day So there are - lines All guys do the same thing and at the end of the day I receive emails sent automatically by their PDAs We used to copy each email body into Excel and convert text to columns After all emails are done there are - lines each day - locations per worker Next we either copy and paste into the templates provided by the company or would use a vlookup in the template to reference off the consolidated table of - lines Problem We don t really have a staff to sit down a do the same thing every day I have tried using a VBA I found on the web to export Outlook messages from a predetermined mail folder But all I m left with now is an Excel workbook with a column of text Each cell in that column contains all - assets from one worker in csv format commas line breaks and all in Excel CSV table text Help Transfer 2007/10 Needed: cells to in the Excel 2007/10 Help Needed: Transfer CSV text in cells to table same cell The next cell down contains another - items from the second worker and so on Now I have rows Excel 2007/10 Help Needed: Transfer CSV text in cells to table amp column filled each with csv text instead of - rows in columns which I need Question Is there a better way to Excel 2007/10 Help Needed: Transfer CSV text in cells to table do this I m quite happy with the way VBA has exported the message bodies into Excel but I need the worksheet to be populated with one row per location not one row per worker I can t even use vlookup now because Outlook has exported the csv text into ONE cell containing - sites assets per email I m pretty sure that with the power of VBAs there must be a smarter way to do this nbsp

Relevancy 76.54%

I have searched for a similar issue but was unsuccessful I need to create a macro to do what will surely one cells to from macro Excel 2007 to copy columns row in files certain second be a miserably repetitive job of transposing data for a whle bunch of files I need to get into each excel file and copy certain data fields from column B B -B turquoise B -B Excel macro to copy certain cells from one row to second files columns in 2007 bright greeen B -B bklue B -B pink and B tan into sequential cells in another file into a single row under a heading A - O I will need to do this several hundred times open a file copy the fields and all the files selected data from a column will wind up in a single excel data sheet in columns I thought I would just do a careful record macro but cannot get to record beyond my first cell copy I have named both files xlsm and changed settings to no avail The source file is Excel macro to copy certain cells from one row to second files columns in 2007 attached and called Source and Detsination is called destination Any help or a macro would be greatly appreciated attached the source file Source I collor codes the field to be copied in the source Excel macro to copy certain cells from one row to second files columns in 2007 as indicated aboue with the header fields in the destination if that helps Thank you VERY much nbsp

https://forums.techguy.org/threads/excel-macro-to-copy-certain-cells-from-one-row-to-second-files-columns-in-2007.958889/
Relevancy 76.54%

This is literally driving me nuts I've created a workbook with about worksheets and prior to sending to the customer 2007 protecting doesn't In why work? cells and Excel locking I want to lock the cells so that they can't be changed without a password So I first select the cells I don't want to be altered and lock them Then I go to quot protect worksheet quot and In Excel 2007 why doesn't locking and protecting cells work? after checking the box that allows people to select cells in the locked section selection of sells in unlocked section checks automatically I enter a password twice and the worksheet is theoretically locked The problem is that it's not I can still change cells in the locked area It didn't work on any of my worksheets So I went back and unprotected the sheets and then selected and locked the cells TWICE after which I protected the worksheets again This time some but not all of the worksheets had locked the cells protecting them from editing I then tried to go thru the same rigmarole but locking the cells three or four times before protecting the sheets for the In Excel 2007 why doesn't locking and protecting cells work? sheets that were still unlocked but that only worked sporadically as well Sometimes I had to dis-allow selection lock the cells in the sheet unprotect it lock the cells again twice and then check the select box and protect the sheet AGAIN That FINALLY worked What the heck is going on Why doesn't locking the cells just lock them This is getting very frustrating Note On one of the sheets even the above rigmarole didn't work I In Excel 2007 why doesn't locking and protecting cells work? can't get the cells to lock no matter what I do What's wrong

A:In Excel 2007 why doesn't locking and protecting cells work?

.
Here is a 'check list' for protecting your workbook :
Password protect worksheet or workbook elements - Excel - Microsoft Office

Just in case there are any errors, you could try the Diagnostics Tool :
Diagnose and repair crashing Office programs by using Office Diagnostics - Excel - Microsoft Office

http://www.sevenforums.com/microsoft-office/151336-excel-2007-why-doesnt-locking-protecting-cells-work.html
Relevancy 76.54%

My spreadsheet has Locked and Unlocked cells and when I Protect the sheet, the only thing I can do is enter data into the Unlocked cells. It does not allow me to format the cell, ie: Bolding orr changing alignment, etc. In Word 2003, I could do this as well as use the AutoSum feature. Am I doing something wrong? I cannot find any answers on the internet or Help screens. Any input is much appreciated.
 

https://forums.techguy.org/threads/excel-2007-unlocked-cells-in-protected-sheet-issue.990242/
Relevancy 76.54%

Hi,

I use excel 2007 for goods that I sell to calculate costs profits etc. Sometimes I have multiple suppliers of a product.

Is there a way in which I can have prices for say 3 suppliers and merge adjacent cells
that will have generic data valid for all 3 suppliers such as size colour etc but then also be able to filter results without getting an error.

At present because cells to the left or right are not the same size it will not filter and I cannot think of another way to do it.

Example:-

Cells A1,A2,A3 are merged and contain the name of the product and cells B1,B2,B3 have the 3 supplier names. Because adjacent cells are merged filtering is not possible or is it?

If not can someone with good excel knowledge advise a different approach?
 

A:Excel 2007: Data filtering issue due to merged cells

i've been thinking about your post since it first came on.

There are a few things i thought of.

Firstly it may just be that you cannot have merged fields and filter the info.

Secondly COuld you have one row for each product and then three columns for three suppliers rather than seperate rows.

The other things i thought about would be if you unmerged the cells. Then used Pivot tables to display the data maybe.

Alternatively Access rather than Excel but im a database geek so i would say that.

I know this is probably not much help but as no one else responded i thought i should give you my thoughts
 

https://forums.techguy.org/threads/excel-2007-data-filtering-issue-due-to-merged-cells.848073/
Relevancy 76.54%

How can I do it?

I basically need THIS to work, but I can't compare two items in the same if apparenlty...

=IF(K12>2400 and K13=0,2400,K12)

I am comparing the totals of daily hours to see if regular hours ended up over 40 (2400 minutes) and whether or not the daily overtime is still zero before I use the regular hours to figure overtime in a timecard system.

Unfortunately that won't work, nor does using & beween because then it concatenates it and always ends up false.

Help!

- Drake.
 

A:Excel 2007 - need to compare TWO cells to determine a value for the current cell...

https://forums.techguy.org/threads/excel-2007-need-to-compare-two-cells-to-determine-a-value-for-the-current-cell.745145/
Relevancy 76.54%

Hi Guys,

First time post so please be nice

I'm having an issue in Excel 2007 where I'm trying to create a chart with information including the formula:

=IFERROR(SUM(IF($F$2:$F$66=$F72,K$2:K$66))/K162,0)

(that was just an example, but all the data calculated uses the same formula)

But I do not want to include any data valued 0 in the chart.

I've tried adding IF(formula=0,#N/A,formula) into the formula as such:

=IF((IFERROR(SUM(IF($F$2:$F$66=$F72,K$2:K$66))/K162,0))<>0,(IFERROR(SUM(IF($F$2:$F$66=$F72,K$2:K$66))/K162,0)),)

But then this makes the value of all cells which does have a figure 0 also.

Please can anyone help? It's driving me mad!!!

Kind Regards,

Vj
 

Relevancy 75.68%

I am trying to highlight specific cells in a row that meet a set or a single criteria. Unfortunately, there isn't an easy way to skip blank cells in executing the conditional formatting. I am able to get the criteria to work, but blank cells get highlighted. When zero is part of the criteria, the blanks come into play. In Excel 2007, other versions operate slightly differently.

Take for example a row of 10 cells/values, example:
175 182 blank 210 220 237 blank 314 124 120

Example of criteria:
a) below 200
b) between 200 and 250
c) above 250

or single criteria upto 250

When ever a 0-250 or below 250 criteria is applied, blanks get highlighted.

Can someone suggest a simple formula to make Excel ignore or skip consideration of these blank cells.

Thanks in advance
 

A:Conditional Formatting to ignore or skip blank cells Excel 2007

you could try doing an AND and not equal ""

like

=(AND(A1<200,A1<>""))

see attached

maybe a better way to do it and one of the excel gurus may provide
 

https://forums.techguy.org/threads/conditional-formatting-to-ignore-or-skip-blank-cells-excel-2007.1036144/
Relevancy 74.82%

Hello everyone,

I couldn't a find a way to do this from Format Cell | Fill | Pattern Style .. - is there a way of cross-hatching cells alternatively, such that two adjacent cells are hashed with left-slanted and right-slanted lines respectively, thus making it easier to discern where one ends and the other begins (especially in the case of merged cells with white background)?

Any help will be much appreciated, anticipated thanks!
(apologies for cross-posting)
 

A:Excel 2007: Cross-hatching cells with alternative left/right-slanted lines

https://forums.techguy.org/threads/excel-2007-cross-hatching-cells-with-alternative-left-right-slanted-lines.935351/
Relevancy 73.96%

Thank you guys, I think I wasn't too explicit last time.

The thing is this. I am working with 2 Columns full of Company's names, I need to separate those rows cells with totally different names, but keep the match with those using similar names, including punctuation, and symbols:

Hence:
A:
A1 BODARK OIL COMPANY
A2 M & G OIL COMPANY
A3 PIONEER NATURAL RES. USA, INC.
A4 ENDEAVOR ENERGY RESOURCES L.P.
B:
B1 BODARK OIL CO
B2 ANADARKO CO.
B3 PIONEER NATURAL RES. U
B4 ENDEAVOR ENERGY RESOUR

As you can see I need a function to tell me that A2 and B2 are N/A, but A1, B1 and the rest are similar, it means its the same company.

PLease... help I have this 800 records Table...

Godzi
 

A:Comparing similar text cells

I am closing this thread please continue to reply here: http://forums.techguy.org/business-applications/645666-compare-similar-cells-excel.html
 

https://forums.techguy.org/threads/comparing-similar-text-cells.646048/
Relevancy 70.09%

Hi there,

may somebdoy please advise (I am pure newbie at Macros):
*I want to create button (I can dot that) and assign Macro to it:
*Copy selected cells (C3:N3)
*Paste values against respective product code (product code can be changed manually in A2)

It happens in the same working sheet (or not a problem if haapens in another sheet)

Many thanks in advance (this would save me some time and efforet)

SM
 

Relevancy 70.09%

Hi

I am wondering if there is a simple macro that would allow me to format cells throughout a workbook based on whether they contain an input (ie number/text etc) vs. a calculation (anything with a formula).

Basically I'm looking for an automated way to colour my inputs in blue font and calculations in blank font across all worksheets in a workbook.

Any ideas would be great.

Thanks
 

A:Excel macro to format input cells vs calculation cells

I wanted to point out that this can be done without using a macro. In Excel you can click EDIT >> GOTO >> SPECIAL and put a check in the option labeled "Formulas." After clicking OK all your formula cells should be automatically selected on the sheet. Once all cells have been selected you can change the font, background color, etc. for all the selected cells at once. If you prefer to use a macro instead you can try the code I provided below.

Code:
Sub FindFormulas()

For Each vcell In ActiveSheet.UsedRange

If vcell.HasFormula = False Then

vcell.Font.ColorIndex = 5

Else

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'Replace with your code to execute if cell contains formula
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

End If

Next vcell

End Sub

Regards,
Rollin
 

https://forums.techguy.org/threads/excel-macro-to-format-input-cells-vs-calculation-cells.912669/
Relevancy 69.23%

Hi , i am working in finance and mostly with numbers .. i have two sheets the 1st sheet includes two columns [order number] and [value] order no. aren't sequentially and each order no. has unique value and both columns already filled... and the 2nd sheet have same columns but only contains a randomly group of [Order Value] and [value] column is empty and i need to fill each value of order no. from 1st sheet ...

A:Excel : How to copy specific cells if 2 others cells matched

Without knowing how your spreadsheet is arraigned, Column Letter & Row Numbers,the best I can offer is a general suggestion of using one of the =LOOKUP() functions,like =VLOOKUP() Somthing like:=IF(VLOOKUP(A1,SheetName!A1:B10,2,FALSE)Where cell A1 is your unique value.MIKEhttp://www.skeptic.com/

http://www.computing.net/answers/office/excel-how-to-copy-specific-cells-if-2-others-cells-matched/20094.html
Relevancy 62.35%

Hello,

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 61.92%

I ve tried searching in this forum but I either didn t find any or I m using the wrong keywords What I m trying to do is 2007 Dates from Tables Comparing Access different get Access 2007 Comparing Dates from different Tables information from Table with information from Table but the criteria is that they if the e g names match change the address in Table only if the date in table is more current than the date in table As far as I can tell from the help files they only provide comparisons between a given date DD MM YYYY which means I can extract thru a query dates before after between a given date E g Dashes just makes the table easier to read Table Name Address Date Aa-- ---- - - Ab-- ---- - - Table Name Address Date Aa-- ---- - - Ab-- ---- - - Query -- gt Table Name Address Date Aa-- ---- - - This Big database contains over mil and the dates range from to end of And I have about smaller databases to compare and update with If anyone can point me in the right direction it would be very much appreciated nbsp

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?

Cheers.

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

http://www.sevenforums.com/microsoft-office/135684-comparing-lists-excel.html
Relevancy 61.49%

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
A B C
Suburb City code
---------- ------ -------

Sheet2 :
A B C D
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 61.49%

Hi,

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,
DV
 

Relevancy 61.06%

How do you compare 2 Excel workbooks,not sheets, at the same time.in order to compare side by side the data contained in each..

Thank you for your guidance

roy[/SIZE]
 

A:Viewing/Comparing 2 Excel workbooks

Open both documents in the same instance of Excel and then click Window/compare side by side.
 

https://forums.techguy.org/threads/viewing-comparing-2-excel-workbooks.839685/
Relevancy 61.06%

Hello,

I am new to macros. I want to compare two excel spreadsheets. Specifically, Book1 is the source file with upto 4 columns populated. Book2 has only one column populated with data that might be contained in any of the 4 columns of Book1. What I am looking for this, a macro to compare each cell of Book2 with the contents of Book1 and if there is a match, to highlight the particular cell in Book1 and in Book2.

I would appreciate any help.

Thank you in advance for your help.
 

Relevancy 61.06%

Hi,

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,

Eddy
 

Relevancy 61.06%

HELP

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.
 

https://forums.techguy.org/threads/excel-comparing-2-separate-worksheets.49484/
Relevancy 61.06%

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.
 

https://forums.techguy.org/threads/excel-comparing-two-worksheets-to-get-data-for-a-third.985438/
Relevancy 61.06%

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:
http://www.excelforum.com/excel-pro...adsheet.html?p=2818323&viewfull=1#post2818323
 

https://forums.techguy.org/threads/solved-excel-vba-comparing-spreadsheet.1056407/
Relevancy 61.06%

Hi,

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 61.06%

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,

=IF(ISERROR(VLOOKUP(A8,$G$1:$H$98,2,FALSE)),0,(VLOOKUP(A8,$G$1:$H$98,2,FALSE)))
lol
Hew

(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!!)
 

https://forums.techguy.org/threads/solved-excel-comparing-2-columns.1003722/
Relevancy 60.2%

All,

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,

miketx
 

A:Solved: comparing data in two columns in Excel

does this help
http://support.microsoft.com/kb/213367
http://office.microsoft.com/en-gb/excel-help/use-excel-to-compare-two-lists-of-data-HA001103915.aspx

or this may ble closer to what you need
http://forums.techguy.org/business-applications/174998-excel-compare-2-lists-highlight.html
 

https://forums.techguy.org/threads/solved-comparing-data-in-two-columns-in-excel.989078/
Relevancy 60.2%

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.

http://www.computing.net/answers/office/excel-challenge-comparing-two-data-sets/19862.html
Relevancy 60.2%

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.

http://www.computing.net/answers/office/how-to-set-icon-in-excel-by-comparing-data-in-2-columns/20422.html
Relevancy 60.2%

I need to take information from a separate excel workbook and add it to the information being output to the four note pad documents The code I have right now is Private Sub BigButtonOfDoing for comparing footer header excel in Importing and and Click Dim target As Worksheet For Each target In Worksheets SheetStorage target Next End Sub Public Sub SheetStorage target As Worksheet Dim iFileForSheet As Integer LR As Long i As Long Dim strFile As String Dim strRec As String Dim qoutes As String Dim comma As String strFile quot C Documents and Settings nicolas amp amp amp amp amp amp amp amp amp amp amp amp amp DDLGanerator output quot amp target Range quot J quot Value amp quot txt quot LR target Range quot C quot amp Rows Count End xlUp Row iFileForSheet FreeFile Open strFile For Output As iFileForSheet For i To LR Print iFileForSheet target Range quot C quot amp i Value Spc target Range quot D quot amp i Value Spc target Range quot G quot amp i Value Spc target Range quot J quot amp Importing and comparing for header and footer in excel i Value Next i Close iFileForSheet End Sub There is the name of the document in cell j The information shown below is from a separate workbook that needs to be opened compared against then read in as the table name as the header and the tablespace initial and next as the footer TableName TableSpace Initial Next PS NFS JK MAIN NFSPRJ M M PS NFS JK HITO NFSPRJ K K PS NFS JK TAIJIN NFSPRJ M M PS NFS HOSPITAL MS NFSPRJ K K PS NFS JK TAIBUTSU NFSPRJ K K PS NFS JK SHARYO NFSPRJ K K PS NFS IMAGE PATH NFSPRJ K K amp amp amp amp amp amp amp amp tablename is the same as in cell j in the main workbook all the information shown here needs to be displayed like so CREATE TABLE PS NFS HOSPITAL MS quot information already inputted by above code quot TABLESPACE NFSPRJ STORAGE INITIAL K NEXT K thanks nbsp

https://forums.techguy.org/threads/importing-and-comparing-for-header-and-footer-in-excel.834051/
Relevancy 60.2%

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

Nos
 

A:Solved: Comparing multiple values in excel

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

https://forums.techguy.org/threads/solved-comparing-multiple-values-in-excel.1069167/
Relevancy 59.77%

Hello,
I am trying to find the minimum number out of a set that has a certain label entered beside it. For example:
Number of OrdersCompany
993 BananaCorp
1200 ZoomZoom
500 Banana Corp
43664 Omletted Way
545 Tomato Inc

I want to find the least amount of orders taken from BananaCorp

Thanks!!!
 

A:Excel - Finding the minimum number while comparing two columns

You have two columns, Orders , Company

Orders Company
500 Banana Corp
993 Banana Corp
43664 Omletted Way
545 Tomato Inc
1200 ZoomZoom

SOrt on Company as a first key and Order as a 2nd Key.
for a visible max order per company.
It's a beginning.
 

https://forums.techguy.org/threads/excel-finding-the-minimum-number-while-comparing-two-columns.758382/
Relevancy 59.77%

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 58.91%

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.

HTH
 

https://forums.techguy.org/threads/solved-basic-excel-question-comparing-multiple-values.944651/
Relevancy 58.91%

Hello,
I'm trying to create a spreadsheet that will inform me about whether students have correctly entered formulas in another worksheet, or if they've simply put in the resulting number. I need to create an if statement that would test whether the formula is entered in the cell correctly, not whether the result of the formula equals a particular number. Don't see any way of doing this. Any help would be appreciated.
Thanks.
 

Relevancy 58.48%

The problem I m concerned about is that Files KB 2007 and in 2007 Are Saving Excel with Fewer Excel 2 Both Now I noticed existingExcel xlsx files are now saving with fewer Kb I have a Lenovo W ds and a W Both computers are running Windows Pro bit The W has Office and theW has Office Both Excel 2007 Files Are Now Saving with Fewer KB in Both Excel 2007 and 2 versions ofExcel are bit as far as I can tell Until recently I was primarily using the W ds but have nowswitched to the W after I saw a larger file losing KB when saved I did a test with a file that had not been altered or openedfor over two years I copied it twiceand added a - and a - to change the names The file was a small Kb file When I opened and saved on the W running Windows with Excel the file saves with Kb When Iopened the file using the W running Windows with Excel the computeron which it had originally been created the file saved as Kb Both saved smaller but the newer computerrunning Excel was much smaller Iam seeing these same types of ratios of reduction in size for much larger Excelfiles as well I have not documented any actual problems or loss of data withany of the files to date and I have used an Ultra-Compare text file comparison which found no differences Is there any reason to be concerned about this And if so what to do As with many people some of my files are the result of manyyears of work and I don't want to do anything to compromise any of the data butI don t know whether or not what I m seeing is normal due to both Excel and Excel becoming somehow more efficiently programmed than previousversions of Excel I have systems whereby I backup sync files to a home networkNAS HD I also sync between thecomputers Your help will be greatly appreciated

http://www.sevenforums.com/microsoft-office/397073-excel-2007-files-now-saving-fewer-kb-both-excel-2007-2-a.html
Relevancy 56.76%

on our database we have a field for entering someones email, however it is set it up so it will only allow 20 characters, how do I expand this field up to 100?
 

A:acess 2007: enlarging data cells

Go to the Table and open it in design View.
Click in the email field and in the lower section change the field size from 20 to 100.
 

https://forums.techguy.org/threads/acess-2007-enlarging-data-cells.789345/
Relevancy 56.33%

i want to create a worksheet, lets call "inventory"

on that sheet will be

item, qty

i will have another sheet called "changes"

on that sheet will be

date, item, change
qty on inventory = sum of change on changes where item(changes) = item(inventory).

any help would be awesome.
 

Relevancy 56.33%

The following question was asked but left unresolved on this site in 2003. It's been 9yrs and I'm hoping Excel has progressed enough to enable an answer.

I want to have a defined group of cells floating beside data on a worksheet. Is this possible?

Thanks for your help
 

A:Floating Cells in Excel

Hi, welcome to the forum (after 9 years? )
I doubt there exists any function you are asking for.
I am not sure what you really want to see but with some vba code (macro) and a user form something could be written that would allow for a 'floating' userform but you will need to trigger if for a particular area of the sheet or a right-click function.

I have no idea but sounds like a nice challenge
 

https://forums.techguy.org/threads/floating-cells-in-excel.1055077/
Relevancy 56.33%

Hi,

I'm trying to create a macro or some kind of script to merge two cell's information into 1.

this is how it looks like:

Row A have first name
Row B have last name
Row C will merge first and last name together.

Row A is the first name and row B is the last name. It's easy using micro; however, the problem is that the report generate randomly everyday and sometimes it will have more entries and some day it will have less entries. is there a way to merge the first and last name together in a new cell with both names. It will need to be able to merge any extra rows that pop up everyday.
 

A:Excel - Merging Cells and more

Sounds strange. Are you sure you don't mean that first names are in column 1, last names are in column 2 and you want them merged in column 3?

That would be (assuming no blank rows):

Sub test()
LastRow = Range("A" & Rows.Count).End(xlUp).Row
Range("C1:C" & LastRow).FormulaR1C1 = "=RC[-2]&"" ""&RC[-1]"
Columns(3).Value = Columns(3).Value
End Sub
 

https://forums.techguy.org/threads/excel-merging-cells-and-more.932739/
Relevancy 56.33%

Has anyone figured out a SIMPLE way to swap data cells?? IE: H3 and G4, or C6 and D8 - just take the data in one, put it in the other, while the other's data is put in the first???
 

Relevancy 56.33%

I have a lot of cells like this:

Code:
<img src="whatever">
<table><tr>
<td class="odd">data here</td>
</tr>
<tr>
<td class="even">different data here</td>
</tr>
</table>
Cells can also just have the img src, just one td class, or 3+ td class parts.

I am trying to find a way in Excel to split up each td class into its own cell. So like the above would end up as:
One new cell would be:

Code:
data here
Another new cell would be:

Code:
different data here
Any ideas? I think I have to use vbScript?
 

https://forums.techguy.org/threads/excel-split-cells.766209/
Relevancy 56.33%

Is there a quick way of finding which cells in a big spreadsheet have "merged cells" ( like Go To Special??)
Thanks
 

Relevancy 56.33%

Hi,

I need some help with this one. I am merging cells in an xls spreadsheet which contain simple strings: for example:

A B C D
1 Smith Bill
2 Larry Jones
3 Hotel Bar The
4 Jones David

should be:

1 Bill Smith
2 Larry Jones
3 The Hotel Bar
4 David Jones

I used the formula B1 =(D1&C1) then did a fill and any cell that needed the prefix added to the cell had it done. Problem is, I need the string values of column B. When I attempt to copy this column to another file I cannot transfer the text.

How do I keep the text info and erase the functional basis of each cell?

Many thanks for any help,

Malcolm
 

A:Excel - merging cells

malcolmd3111 said:

How do I keep the text info and erase the functional basis of each cell?Click to expand...

1. Select the formulas

2. Copy

3. Edit > Paste Special > Paste Values > OK.
 

https://forums.techguy.org/threads/excel-merging-cells.620159/
Relevancy 56.33%

I have a remote user who is quite Excel savvy. She has used an existing workbook as a template for a new one and when she tried to alter a named cell she ran into problems. I talked her through the process of deleting the old cell name and adding a new one but a formula that referenced the named cell failed to work. I suggested she save and close the workbook and reopen it. This seems to have done the trick but she'd like to know if there's an easier way to do this for future reference. Is it simply that this process has to be followed if a sheet or book with a named cell is to be altered in this way?

Thanks in advance for any help you give.

N03L.
 

A:Naming cells in Excel

Some names are used in multiple worksheets, which is just plain bad form. Might what to choose that Show Dependents before renaming named ranges too.

Why delete the old name? Why not just add the new one? A cell can have two names.
 

https://forums.techguy.org/threads/naming-cells-in-excel.143564/
Relevancy 56.33%

Hi
I have a excel sheet that some cells are password protected i have no idea what the password is any ideas how i can find the password or any software that will find it, so i can change the cell contents. Thanks

A:Excel un password cells

Open Excel. Go to the help function (?), and type in password. You will get a lot of information.

I don't know how easy it is to completely remove password protection if you were not the person that developed the worksheet.

http://www.sevenforums.com/microsoft-office/51108-excel-un-password-cells.html
Relevancy 56.33%

on a worksheet in cell E1 is a name copied from another worksheet in the workbook and in cell E22 is a calculated figure based on entries made in other cells, what i would like to happen is when the figure in cell E22 reaches a certain figure i.e. 50, cells k19-q19 change colour, i would prefer to use vba code, any idea's
thanks
 

A:Solved: excel cells

Try This....
Code:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim wsk As Worksheet

Set wsk = Sheets("Sheet1") 'Change this to whatever your sheet is named

If Target.Address = "$E$22" Then

wsk.Range("K19:Q19").FormatConditions.Add Type:=xlExpression, _
Formula1:="=$E$22>=50"

wsk.Range("K19:Q19").FormatConditions(1).Interior.Color = 255 'Color is Red

End If

End Sub
 

https://forums.techguy.org/threads/solved-excel-cells.885539/
Relevancy 56.33%

How can I add the cells within a worksheet, not the contents of the cells, the number of cells into another cell within that same file.
 

Relevancy 56.33%

I think that this is a very simple problem but I can't find the solution.

Is there a command or function in excel that will allow me to take 48 cells (columns) of data & place it in a 6 X 8 table?

ron40
 

A:Arranging cells in excel

Dont know if this is gonna help, but you can make the cells in a row to appear as the cells in a column by doing a 'Paste Special' and selecting 'Transpose' in the dialog coming up.
 

https://forums.techguy.org/threads/arranging-cells-in-excel.400501/
Relevancy 56.33%

Hi, Using Excel I would like to create a macro:
I have several rows of products, I enter a qty next to some of them but not all,
would like to have a macro that filters and reduces my list to the rows that have a qty only.
Please help with macro code. Thanks Frosty
 

Relevancy 56.33%

Hi,

It's been a while since last I used Excel and the problem is that I forgot how to make lines. What I mean is I remember that when I click at an edge of a cell then comes a line, so you can create columns, boxes or whatever! Normally I don't want to make a line around the whole cell, in that case I know how to do it(right click on the cell/ format cells/ border).

I want to make only a single line where I click with left button. For example if I click at the bottom of the cell it comes a line only there and the same story if I click only on the right, left and on top of the cell.

It's been already two days trying to make these lines but I really can't. So my question, is there any help?

Thanks in advance,
miniman
 

Relevancy 56.33%

I designed a Visual Basic form from within excel to input currency values onto a spreadsheet. The cells on the spreadsheet have been formatted to 'currency', but when I type in a value on the form thats only a number (without the dollar sign) it only inputs a number on the spreadsheet even though the spreadsheet cell is formatted for currency. Why does this happen?, is there a way to make the VB form input boxes automatically convert a normal number to currency?
 

https://forums.techguy.org/threads/excel-formatting-cells.58627/
Relevancy 56.33%

I hope someone can help. This is driving me nuts.

I want to select and copy groups of cells and paste them into something else (final destination actually a page of a blog). I want to take the gridlines and the general appearnce with it.

I am highlighting, either Ctr + C, followed by Ctr + V, or via the edit dropdown.

ONCE it came out right, all other times I just get cell contents.

Please help!!!!
 

Relevancy 56.33%

So here is my problem I would like to transpose this A B C D E Into this A B C D E BUT some of the items in in Cells Excel Transpose my lists are varying in number of rows in the cluster Th Church Of Transpose Cells in Excel Christ Scientist MacDougal Street New York NY - - Categories Charitable amp Non-Profit Organizations Religious Organizations Nd Street Y - Transpose Cells in Excel Bronfman Center For Jewish Life Lexington Avenue New York NY - - Marty Maskowitz Manager Categories Charitable amp Non-Profit Organizations Youth Organizations Centers amp Clubs Five Avenue New York NY - - Categories Charitable amp Non-Profit Organizations Every cluster of info starts with the name and end with the category but some have added info and some not all the info Any suggestions on how I could transpose this cleanly This file is HUGE so doing it by hand would be hard I was thinking if there was a way to sort this by all the clusters that have items then by items and so on nbsp

A:Transpose Cells in Excel

Every cluster of info starts with the name and end with the category, but some have added info, and some not all the info.Click to expand...

Due to the complexity of your requirement, I can't give an easy answer. Your third cluster only contains an address and phone #; that doesn't make sense. What are you trying to accomplish?

Actually, if I were dealing with a large file of this type in Excel, I would probably transfer the data into Access; a database seems like a much better way to handle such data.
 

http://www.pcreview.co.uk/threads/transpose-cells-in-excel.4068312/
Relevancy 56.33%

Using excel, I am trying to format certain cells that have formulas attached to them - this way I cannot accidentally delete a formula by typing in the cell.

I do NOT want to lock the entire document, as I need access to the other cells (i.e. to type in the data). There must be a way to lock the formula of just one individual cell .... ANY IDEAS?!?
 

A:EXCEL HELP! - Locking Cells

Hi hoosieriu,

You in essence have to think of this the other way around.

By default all cells are already locked, but this has no affect until the s/s is protected.

So what you have to do is unlock the cells where input is allowed , then protect the spreadsheet.

1. Format / Cells / Protection Untick 'Locked'
[Click on the intersection at the very top left of row and column to selct ALL cells if it would be quicker to unlock all first then lock specific cells afterwards]

2. Tools / Protection Protect Sheet

hth

Hew
 

https://forums.techguy.org/threads/excel-help-locking-cells.411855/
Relevancy 56.33%

I have a recurring problem when using Microsoft Excel. When I click on one cell in the worksheet in automatically selects the one or two cells next to it. No matter where I click I will not allow me to select one singe cell. This makes it twice as difficult to use formulas because it selects extra cells when doing so.

Anyone have any ideas as to what is causing the multiple selection?

All ideas welcome!
 

Relevancy 56.33%

I've created a row of calculated values based upon variables (some of which might be 0). The resulting row of calculated values will sometimes display #DIV/0. So, I used conditional formula, =IF(A2=0,"",A4) ... to properly display results, with blank cells in place of the #DIV/0. Now I'd like to graph the results. When I do, I get a 0 value in place of the blank cell (which has resulted from the conditional IF function). When I do a =ISBLANK on the blank cell, the answer is .F. When I do a =ISTEXT on the blank cell, the answer is .T. Does anyone have any ideas on how to create a formula which will eliminate the #DIV/0 with a truly blank cell, so that the graphical representation won't represent that data point?

Many thanks.
Jack
 

A:Excel? Blank Cells

>> =IF(A2=0,"",A4)

=IF(A2=0,#N/A,A4)

instead should suppress the 0 values in the chart. If you then don't want the #N/As to show on the sheet, use conditional formatting (e.g. Formula is =A2=0, with a white font Format).

HTH,
Andy
 

https://forums.techguy.org/threads/excel-blank-cells.235323/
Relevancy 56.33%

Hello all Well I m rather new to Microsoft Excel and we have just opened up a storage facility at work which is now up and running I have a spreadsheet laid out with the product A then chargable amount B the charge per week C then the total per week D but how can I get B C to mutiply with the total being displayed in D Also im not sure if this is correct but I need to somehow lock this formular to just row so I can continue the process onto row with a seperate formular so therfor if stock is taken or received it in excel???? Multiplying cells will calculate a different total Then say at the end of the month I can work out a formular going down the D collum for a total of the storage Your help would be much appreciated I m not sure if im coming accross this way but im a very desperate man right now Many Thanks Scott nbsp

A:Multiplying cells in excel????

in D1 type =b1*c1
use the summation button (it looks like an m on it's side) in the cell at the bottom of your values in column D
or you can type in =sum( and then highlight the values in column D and then close the brackets with ) and it will do the same thing.
 

https://forums.techguy.org/threads/multiplying-cells-in-excel.366292/
Relevancy 56.33%

I have a price list that is done in Excel and some of the items need to have the text wrapped because the description is longer cells Formatting Excel in then the row allows to print out on x paper The problem is whenever I add a new title they are alphabetical Formatting cells in Excel and insert a row it messes up the formatting of any of the items below the inserted row by hiding the wrapped text of items w longer descriptions The line description of a wrapped text item doesn t show - only what wraps onto the second line is visible - and I then have to go down the list and resize all the items that require more room I have already set the rows to wrap text under formatting for the whole page How can I get this file to automatically allow the row height to adjust for the wrapped text when needed as well as larger font sizing All the row heights that have larger font sizing in them will also squish down to only show of the text so that the top portion of the letters are Formatting cells in Excel cut off I dread editing this file because I have to do a major overhaul on all the row heights whenever I insert any new rows for new items HELP THANX nbsp

Relevancy 56.33%

IT's been a while since I used excel.

How can I select a group of cells and make them float above the others, so that I can move them around the screen at will?

I don't want to freeze cells, I need them to float

Thanks
 

Relevancy 56.33%

Hey, i have a friend who owns a Resturant and i he wanted to do a roster by getting one cell, putting a diagonal line from right to left and putting text on both sides of the line in the single cell... i have seen this done but i dont know how to do it myself... thankyou
 

Relevancy 56.33%

Hi all
I need to number a large amount of cells. is there anyway you can highlight 200 cells in a collum and then automatically number them from 1 to 200 in one go.
Thanks in advance for the help.
 

Relevancy 56.33%

I wish to set up a spreadsheet on a shared documents area of a network. The spreadsheet needs to have preset cells with fixed content and the rest open to edit until such time as a value is entered. These cells must then automatically lock. I, as administrator, would be able to unlock, edit and delete the cell value.
Any ideas?
 

A:Excel - Locking cells

in excell when saving click tools wihch is in the top right of the dialog box then select the security option
 

https://forums.techguy.org/threads/excel-locking-cells.343934/
Relevancy 56.33%

This has more than likely been answered loads of times but maybe I am searching in the wrong place.

I have a spreadsheet in which I record expiry dates of certificates and I need it to do the following:

Less than today, expired certificates date to show RED

Today plus 14 days, due to expire to show AMBER

Today +14 up to 60 due to expire between day 15 upto 60 days,to show in GREEN

I managed to work our for one colour which gave me pre today and upto +60 days but only in RED.

Any help greatly appreciated.
 

A:Conditional formatting Cells Excell 2007/2010

First select the range that you want this formatting applied for.

Then create a new rule for each of the types below. I think 'Format only cells that contain' should be fine for what we need here.

1. Cell Value, less than, =TODAY()
2. Cell Value, between, = TODAY(), =TODAY + 13
3. Cell Value, between, = TODAY() + 14, =TODAY() + 60

The 2nd one is +13 to account for the overlap that would occur if both 2 and 3 were set to +14. As you can see if they were both +14 both rules would become true and you wouldn't have an easy way to control which one was shown.

Hope this helps!
 

https://forums.techguy.org/threads/conditional-formatting-cells-excell-2007-2010.1039120/