Computer Support Forum

Excel: Checking values on multiple cells and transferring values to 1 cell

Question: Excel: Checking values on multiple cells and transferring values to 1 cell

Hello Everyone,
(This is kind of long but I didn't know how else to explain everything)

There are two parts to my question below.

I have a spreadsheet with roughly 40 columns

An Example of some of the columns would be:

Code:

B1 = Client Name E1 = Travel F1 = Backup G1 = PC Install H1 = Data Recovery | X1 = Total | AE1 = Summary
B2 = Tony Smith E2 = $70.00 F2 = $0.00 G2 = $95.00 H2 = $0.00 | X2 = $165.00 | AE2 = (See Below)
B3 = Tom Daniel E3 = $70.00 F3 = $0.00 G3 = $0.00 H3 = $725.00 | X3 = $795.00 | AE3 = (See Below)
B4 = Rene Little E4 = $70.00 F4 = $125.00 G4 = $95.00 H4 = $995.00 | X4 = $1285.00 | AE4 = (See Below)

Currently this spreadsheet is being analyzed to be used to track field technician jobs and figure everything out for accounting.

I have been asked to please make a cell with a list of the information above so that accounting can just copy and paste from that cell into quickbooks in order to send these people out a receipt instead of having to retype everything into quickbooks. This list has to be based upon whether or not there is a value greater than 0 within that cell then it will grab both the heading cell in the first row and the value of that cell it was checking and place it as a line item in another cell.

I know how to do half of what they are asking and if I was dealing with 1 column I can do the other half.

I know how to list items in 1 cell using the char(10) function within the cell.

I don't know how to check multiple cells at once to see if there is a value greater than 0 and then if there is to take the cell 1 text with a : and then the value of the cell I am checking into another cell as a line item.

Some examples would be the following. From the chart above:
Let's take row 2.

Code:

B2 = Tony Smith E2 = $70.00 F2 = $0.00 G2 = $95.00 H2 = $0.00 | X2 = $165.00 | AE2 = (See Below)

1st the code would look at B2 and see that there was a value and print that to AE2 & char(10) &
Tony Smith

Next, the code would look at E2 and see that there was a value greater than 0 and then print E1 with a colon ":" and E2 to AE2 & char(10) &
Tony Smith
Travel: $70.00

Next, the code would look at F2 and see that there is not a value greater than 0 and skip it and go onto G2. It would see that G2 has a value greater than 0 and then print G1 with a colon ":" and G2 to AE2 & char(10) &
Tony Smith
Travel: $70.00
PC Install: $95.00

It will go through and look at every cell in that row and perform that same function for all of them.(Currently cells E:Z) until AE looks like:

Tony Smith
Travel: $70.00
PC Install: $95.00
Total: $165.00

Thanks for any help

If I missed anything let me know.

Wayne

Relevance 100%
Preferred Solution: Excel: Checking values on multiple cells and transferring values to 1 cell

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

Answer: Excel: Checking values on multiple cells and transferring values to 1 cell

16 more replies
Relevance 153.99%

This is the code i got so far it will go though and pick the users that have the appropriate status "A" and paid "No". I need it to then copy those users that are appropriate to the Mail Extract sheet. I think i am ment to have a Dim or something in there arn't I. The macro is under the Modual 1 Thingy... If you can help me please do so PLEASE. The data to be copyed is on the Band Members sheet.

=======================================================
Sub MailExtract01()

!!! I know i need some DIM stuff here too!!!

'If filtering
Sheets("Band Members").Select
Range("A3").Select
ActiveCell.Offset(0, 5).Range("A1").Select
For Each Cell In Range("Status")
If ActiveCell.Text = "A" And Selection.Offset(0, 2).Text = "No" Then

!!! This is where i need the copy code to go, I think !!!

End If
Next Cell
Sheets("Mail Extract").Select
Range("A1").Select
End Sub
=======================================================

I need the code to filter though every person and check if the status is “A” and the Paid is “No”. After the filtering I need every person with an “A” under Status and “No” under Paid to be copied to the “Mail Extract” worksheet. I have got all the code to filter though the cells but how do i get this to then copy the appropriate members to the "Mail Extract sheet. There is a code of the file i am working on attached to this post.

Any help will be ... Read more

More replies
Relevance 153.99%

This is the code i got so far it will go though and pick the users that have the appropriate status "A" and paid "No". I need it to then copy those users that are appropriate to the Mail Extract sheet. I think i am ment to have a Dim or something in there arn't I. The macro is under the Modual 1 Thingy... If you can help me please do so PLEASE. The data to be copyed is on the Band Members sheet.

=======================================================
Sub MailExtract01()

!!! I know i need some DIM stuff here too!!!

'If filtering
Sheets("Band Members").Select
Range("A3").Select
ActiveCell.Offset(0, 5).Range("A1").Select
For Each Cell In Range("Status")
If ActiveCell.Text = "A" And Selection.Offset(0, 2).Text = "No" Then

!!! This is where i need the copy code to go, I think !!!

End If
Next Cell
Sheets("Mail Extract").Select
Range("A1").Select
End Sub
=======================================================

I need the code to filter though every person and check if the status is “A” and the Paid is “No”. After the filtering I need every person with an “A” under Status and “No” under Paid to be copied to the “Mail Extract” worksheet. I have got all the code to filter though the cells but how do i get this to then copy the appropriate members to the "Mail Extract sheet. There is a code of the file i am working on attached to this post.

Any help will b... Read more

Answer:Excel: Checking values in multiple cells and transferring values to another worksheet

16 more replies
Relevance 97.73%

I have created a bonus plan for my team. It has 3 factors that could reduce the value. If all 3 factors = 100%, they get 100% of bonus. If any factors are less than 100%, then their bonus is reduced by the % of the lowest factor to give them their payout. Example1: If any factors are 0%, bonus is 0%, Example 2:If the factors are 100%, 75%, and 50%, then payout would be at 50% (the lowest factor). Below are the parameters of the 3 factors. Factor A = 100%, 75%, 50%,0%Factor B = 100%, 75%, 0%Factor C = 100%, 50%I hope this is clear enough. Thanks in advance for your help inautomating this recurring process for me if possible!

Answer:Need Excel to return value based on multiple cells & values

Your text doesn't match your math."If any factors are less than 100%, then their bonus is reduced by the % of the lowest factor to give them their payout. ""reduced by the % of the lowest factor" means that:If any factors are 0%, bonus is "reduced by" 0%I assume you mean that the bonus is "reduced to" the percentage of the lowest factor. That is the only way a Factor of 0% would equal a bonus of 0%.Assuming that you meant "reduced to", let's go from there.You didn't supply any information related to the layout of your data, so it's hard to be specific with a solution.Assuming that the percentage for each Factor is stored in a cell, you should be able to use the MIN function to determine the Bonus amount.For example, let's say you start with this layout:
A B C D E
1 Max Bonus Factor A Factor B Factor C Actual Bonus
2 $1,000 100% 75% 100% =A2*MIN(B2:D2)
The formula in E2 should return $750, which is 75% of the Max Bonus.Is that what you were looking for?Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.message edited by DerbyDad03

3 more replies
Relevance 94.83%

....A................. ........B ..........................C

1. AMDRG.SA ........AMDRG.CCM.......CTFACmpMkt='AMDRG.SA' + 'AMDRG.CTA';

I am not sure how good that is going to look once i post this, but here is a rundown of what it is.

Lets say those are 3 columns, A, B, and C. I would like to have a macro or script that will take what is in column B, which is also inside the text of column C, and change the text AMDRG.CCM to what is in column A(AMDRG.SA). So when i am done, the info in column C will look like this...
CTFACmpMkt='AMDRG.SA' + 'AMDRG.CTA'
Column A and column B will always line up with eachother, but column C will not always line up right beside the cells associated with what the replacement text is.

Thanks for any help that you can give!
 

Answer:Multiple find and replace cell values in excel

Can you post a sample workbook with a few more records?

Rollin
 

3 more replies
Relevance 93.96%

OK... Here's something I have been looking at for a while and getting myself all in a tizz...!!!

I have 2 worksheets within 1 workbook - see attached example. The first contains unique references (ABx in Column A) with associated processes in Column B. Each cell may contain more than one process - comma seperated. The second sheet contains unique references (XYx in Column A) which map back to ABx references in Column B (again, each cell can contain multiple mapping references - comma seperated).

e.g.
Ref - Process
AB1 - Create, Update
AB2 - Create, Read
AB3 - Delete, Update

Ref - Maps-to
XY1 - AB1, AB2
XY2 - AB1
XY3 - AB2

Now, what I am looking to do is within sheet 2 (containing the XY references mapped to AB references) is create a column which will display the corresponding processes without duplication.

e.g.
Ref - Maps-to - Process
XY1 - AB1, AB2 - Create, Update, Read
*Note that AB1 and AB2 both contain Create which should only be returned once.

I have tried using =VLOOKUP(B2,'Reference Sheet'!A:B,2) but this only uses the first reference to return results and therefore using the above as an example the result would not include "Read".

Any takers? Hope someone can help...

Thanks in advance!
 

Answer:Solved: Excel: Cross Reference multiple values in cell

16 more replies
Relevance 84.68%

Hi. I'm building an employee schedule into Excel. I am using a seperate tab for each different areas of the business. In each tab I have a column that indicates if the shift is a "Custom Program". I would like to have a seperate tab that brings all "Custom Program" shifts together on one page, drawing from all the other tabs.

I know how to reference specific cells onto the new tab, and I know how to do conditional formatting within a tab to find and respond to a specific word, but that's not quite what I need.

-Is there a way to reference a particular value (every time the words "Custom Program" appear) from Sheet 1 and put it in Sheet 2?

-Once the "Custom Program" values are sent to Sheet 2, I need the rest of the data in those row to come with them. How do I do that?

I hope I've explained that somewhat clearly. Thanks for your help.
 

Answer:Excel: Referencing Values (Not cells)

9 more replies
Relevance 84.1%

I have a sheet with 2 simple columns: Date and Price. I have imported the dates (##/##/####) and the prices ($###,###) by copy/pasting from the search results given to me by a niche database program I use. When the cells paste in, they all have the format "General".

When I try to format the "date" column into dates, it _does_ change the format as far as the cell is concerned, but the content of the cell doesn't adapt to the new format. For example, I have the date as 3/05/2001 and when I change it to a date format of MMM D, YYYY the content should change to March 5, 2001 but it doesn't. It is as if all the cells are forced to stay as text regardless of what the formatting is that I'm applying.

Same problem with the price column: if I change the format to include 2 decimal points, that format does apply to the cells, but the content of each cell remains without a decimal or anything following, as if the content is just text.

I have like 1000 rows in each column, and plan to do this analysis of the database's results frequently, so I'm hoping the answer isn't just to retype the data. There's got to be a way to copy/paste or export or something. Maybe I could copy/paste into notepad first to scrub out any formatting or locking from the niche database program?
 

Answer:Excel 2007 Cell Values Won't Take On Characteristics of Newly Applied Cell Format

Good news: Made some progress. In thinking that maybe each value had the textual single-quote forcing it to act like text, or maybe if I find/repaced all the dollar signs and commas that had been imported, I accidentally discovered that each and every value in my imported columns has a following space!

Bad news: Seems like Excel has a bug that thinks that if I say "Find=[singleSpace]" "Replace=[null]", then I should be given an error saying "Excel cannot find any data to replace". I think I'm doing the find/replace correctly because it worked on the dollar signs and commas.

Anybody know a workaround for the bug?
 

1 more replies
Relevance 83.81%

Hey,
I've got Microsoft Excel 2003. I am working on organising tons of data (words, not numerical) that's given to me in a bunch of spreadsheets and I need to consolidate all of them into one. The original spreasheets are not organised very well, so a writing of a macro isn't an option. Copy and paste it is. However, there are some sections where I need to combine the information in multiple cells into one.

Example:
cell1: John
cell2: is the
cell 3: manager of the
cell4: company.

Now it'd be really nice if there was some sort of way to copy cell1-4 and paste it into cell 5: John is the manager of the company. Anyone know a way possible? It's taking me forever since I have to go into the fx, not just copy/paste the cell.... not to mention that there are some instances where I have to combine 10+ cells into 1.

Thanks...
 

Answer:Excel help... any way to consolidate values in multi cells into one?

This problem has been around for a long time. I know that on anything pre-2k3 it was a little more difficult as copy/paste, of course, doesn't work exactly as you need it to in this instance.

If the cells are right next to each other, you might be able to just merge all the cells together. You can't do this in 2k as it will erase all but the first cell.

The other thing you could probably play around with is the concatenate function (at least, I think that what it is). You'll have to google for exact formulas but I think it's something like con(cell1, cell2, etc). It's been a while since I tried that. Downside to this is if you have a bunch of cells in a lot of random postions, you'll end up doing a lot more formula writing than actual copying.

Or, could you export the excel document as a word file? Then you could take out all the spaces / tabs with the replace function. Take a little time, but it'd probably work.
 

2 more replies
Relevance 83.81%

Hi,

Im currently having trouble, i have some code to search for a value within 3 spreadsheets but what id like is for it to also return a value on the same row.

e.g
Dave 111111
Claire 112233

so instead of returning just dave, id like the number returned also.
 

Answer:Excel macro: Return values from 2 cells on same row

9 more replies
Relevance 82.65%

Hi,

Got a few questions which tie in to what i am doing so i'll try to explain as best i can.

I want to be able to have excel automatically arrange the order in which it displays the information based on the value or information in a cell. Eg i have a list of things i sell online and I track their performance using the spreadsheet were i have a sheet for every product type which are linked to a master snapshop sheet which has the prices, weight and other info about the items. The product name on this main sheet is hyperlinked to the sheet containing the product and in turn the data in that sheet is formulated back to the main snapshot the aim being as little data input as possible to calculate everything.
I would like to be able to have the snapshop mainsheet automatically put for example the best performing product at the top of the list. At the moment a have to select the cell and expand the data range but this is erratic as i don't think i do it properly. Sometimes it messes all the data up. I am not that good with excel so i am probably going to be told why don't you do this or that etc but that is ok if anyone has an idea.
Anyone able to help?
Apologies if its difficult to follow but i will give more info if requested.
If someone has a link to a webpage that can help would be great also.

Regards,
lee_1133
 

Answer:How do you Auto arrange cells in excel based on their values?

16 more replies
Relevance 82.65%

I have a macro from MrExcel.com that parses each row from an Excel spreadsheet into a separate word document and saves each document to My Documents as "File-Row #.doc". For example, row 2 is saved as: File2.doc, row 3 as File3.doc, etc. to the last row with data. The macro starts on row 2 to skip the column headers.I would like to modify the macro to have the file name equal the concatenated values from the first two cells in each row (Columns A and B from row 2 to the last row with data).An example of a simplified version of the spreadsheet: A B C1 Date as Text Record # Record Status2 2012-05-28 APF-2012-1940 Closed3 2012-05-29 ICM-2012-1987 In Process 4 2012-06-02 PAT-2012-2317 Awaiting ClosureOnce the macro is run, the results from the above would be 3 word documents with the file names as below:Row 2 file name is: 2012-05-28 APF-2012-1940.docRow 3 file name is: 2012-05-29 ICM-2012-1987.docRow 4 file name is: 2012-06-02 PAT-2012-2317.docThe macro as it currently exists:Sub ControlWord() Dim appWD As Word.Application Set appWD = CreateObject("Word.Application.8") appWD.Visible = True Sheets("Data").Select FinalRow = Range("A9999").End(xlUp).Row For i = 2 To FinalRow Sheets("Data").Select Range("A" & i & ":N" & i).Copy Sheets("Template").Select Range("A" & i & ":N" & i).PasteSpecial Tr... Read more

Answer:Modify macro to use values in two Excel cells as file name

In the future, if you are going to post data or code in this forum, please click on the blue line at the end of this post and read the instructions on how to post example data and VBA code in this forum.As far as your current issue, this syntax is probably not converting to what you want it to be:Range ("A" & "B" & i)For i = 1, this would seen to VBA as:Range("AB1")I would try something like this within the loop:tmpFilename = Range("A" & i) & " " & Range("B" & i)...
..
...
appWD.ActiveDocument.SaveAs Filename:=tmpFilenameThis will build the Filename from the values in A1 and B1 with a space in between the 2 values.You should also be aware that you might run into problems with the dates in Column A.If VBA picks up the dates as 05/28/2012, then your code will fail because slashes can not be used in a filename.It will depend on how the default dates are set up on your system.Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

3 more replies
Relevance 82.36%

Hi,

I built a large macro a while back that I need to reduce because with the recent additions I have made, I got "Procedure too large" as an error upon execution. I've already condensed most of my coding to something more elegant (I keep learning new things every time). Now I'd like to reduce the following code :

Do Until ActiveCell.Column = LastCol + 1
If ActiveCell.Value = "resRespondent" Or ActiveCell.Value = "ResLastCallDate" Or ActiveCell.Value = "LastCallStartTime" Or ActiveCell.Value = "resCallCount" _ ''And it goes on like this for many more values
Then
ActiveCell.Offset(0, 1).Select
Else
ActiveCell.EntireColumn.Delete
LastCol = LastCol - 1
End If
Loop

This is actually a big chunk of code and I have many more of these chunks in my macro. The problem is always having to repeat "Or ActiveCell.Value = " before each test. Something along the following idea would be great:

If ActiveCell.Value = "this", "that", "or this", "or that" Then

Thanks,
Peatawn

Answer:VBA - Test cell for multiple values

You can use SELECT CASE instead of IF, which allows such multiple values when comparing the same variable to many constants:


Code:
SELECT CASE ActiveCell.Value
CASE "resRespondent", "ResLastCallDate", "LastCallStartTime", "resCallCount"
ActiveCell.Offset(0, 1).Select
CASE ELSE
ActiveCell.EntireColumn.Delete
LastCol = LastCol - 1
END SELECT
Which is functionally equivalent to your example. Try it, as I've not actually tested it.

2 more replies
Relevance 82.07%

In sheet 1 Column A has current values, Column B has the new value for each entry.

Column A Column B
Old value New Value

Document 1 SOP-001
Document 2 SOP-002
Document 3 SOP-003

In sheet 3 has a list of entries
Column A

Document 1
Document 3
Document 3
Document 2
Document 1

What formula can I use to automatically replace the value SHEET3:ColumnA with the appropriate value from SHEET1:column B? The lists are quite long. Resulting in a list on sheet 3 that looks like this:
SOP-001
SOP-003
SOP-003
SOP-002
SOP-001
 

Answer:Solved: Excel-Substitute values from a list of new values

Hi bongiojf, welcome to TSG.

You can do this with a vlookup formula, assuming that the old values are in the leftmost column and that they are sorted alphabetically.

On sheet 3, in column B (or any blank column) use this formula for the first cell and then copy it down the rest of the column:

=VLOOKUP(A1,Sheet1!$A$1:$B$3,2)

Change the range $A$1:$B$3 to be the range of data you have on Sheet 1. The number 2 at the end of the formula indicates that the new values are in the second column from the left. If they are not, change the number accordingly. For example, if they are in column D, the value would be 4.

Once you have the new data in your blank column, you can copy the column and paste special/values over the existing data on sheet 3.

Hope that helps.
 

1 more replies
Relevance 81.78%

Hi All,I am running a windows batch file which will accept a single value as a parameter.The value is either 0 or 1.Inside the batch file i want to check if the parameter is not 0 or 1 then it will generate an error.It is something like this.IF <PARAMTER VALUE> NOT IN (0,1) THEN  GOTO :PARAMETER_ERRPlease let me know how to do this. I am using windows XP and windows 2003 server

Answer:Checking multiple number values in a windows BAT file

Maybe something like this?Code: [Select]@Echo Off
If [%1] EQU [] (Echo Param required & Goto End)
If %1 LSS 0 (Goto ParamError) Else If %1 GTR 1 (Goto ParamError)
Echo Param passed was %1
Goto End
:ParamError
Echo Wrong parameter passed. Must be 0 or 1
:End

2 more replies
Relevance 80.91%

I am trying to add a series of cells that contain a mixture of positive and negative values.
Using =sum(b3:f3) is not providing the correct answer.
Can someone help please?
 

Answer:Solved: Excel addition of +ve & -ve cell values

6 more replies
Relevance 80.91%

I would to insert a row in excel only if by comparing two valves in seperate sheet and if the value matches copy the entire row paste it by inserting a new row below it. can this be done easily with formula or should we have to go macro. please help me with this

Answer:How to insert a row in excel based on values in the cell

A formula can not insert rows, so you will need a macro.We will need more details before any help can be offered. What values are be compared, which sheets, etc.Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

2 more replies
Relevance 80.91%

I'm having difficulty figuring out a function for what I want to do...The first two columns in my document coincide with their respective values. Column A (File Number) is coded for Participant Description (Column B) and the values in each row are associated with one another...However, I have a 3rd column (Article Number) whose values mean the same as the File Number column, but the the rows don't match up.What I need is a fourth column. In this column I'd like the value from the Participant Description column to be imputed if the Article Number and File Number column are the same.An example from the attached spread sheet.A2 = 7, B2 = 6, C2 = 7 - I need D2 to equal B2That is easy enough, but this is where I get lost...A2 = 7, B2 = 6, C3 = 7 - I need D2 to equal B2Also,If you look at the document, you'll see that that Column A and Column C don't match up, but they represent the same Document ID number... Column B represent a category that corresponds to the Document ID in Column A - Without writing 250 if statements I would like to see if I can get the Value in Column B (For a corresponding value match between column A and C) imputed into Column D.So in this attached file... Column D represents the cell whose value I want it to represent...Thanks in advance for your help on this.http://www.mediafire.com/file/tnwby...p.xls

Answer:Excel - Fill in Value if two cell values matc

Put this in D2 and drag it down.=OFFSET(INDIRECT("A"&MATCH(C2,$A:$A,0)),0,1)MATCH(C2,$A:$A,0) will find the first occurrence of the value in C2 in Column A and return its position, which is 2.INDIRECT("A" & 2) will "build" an address string by appending the result of the MATCH function with "A", evaluating to A2.OFFSET(A2,0,1) will return the value in the cell that is OFFSET from A2 by 0 rows and 1 column, which is B2.

3 more replies
Relevance 80.91%

Is it possible to use an IF statement to set the value of not only the cell the formula is in, but also other cells.

i.e. IF (G3="Yes" THEN Cell B3 = "xxx" and Cell D4="yyy" ELSE "False")

Or is there a way of doing this without the IF function, this would be very useful. Thanks.
 

Answer:Excel - Setting mulitple cell values with IF

Not unless you're "in" the other cells.. or using VBA. A formula in a cell cannot influence any other cell unless you're directly in that other cell (i.e. another formula).
 

1 more replies
Relevance 80.91%

I am having trouble doing the following:

I have an Excel workbook with two sheets. The first sheet contains two fields, one for first name and one for last name.

On the second sheet, I want to combine the two into one column called borrower name. Here is the formula I'm using:

='sheet1'!A1 & ", " & 'sheet2'!B1

this works on the same sheet as the source data (sheet1), but is ignored when on the second sheet.

Any ideas?
 

Answer:Combining cell values on different Excel sheets

HeavyBall said:

I am having trouble doing the following:

I have an Excel workbook with two sheets. The first sheet contains two fields, one for first name and one for last name.

On the second sheet, I want to combine the two into one column called borrower name. Here is the formula I'm using:

='sheet1'!A1 & ", " & 'sheet2'!B1

this works on the same sheet as the source data (sheet1), but is ignored when on the second sheet.

Any ideas?Click to expand...

Hi

If you are combining data from just one sheet then your second reference should be Sheet1 and not Sheet2 - or have I misunderstood?

Regards
 

1 more replies
Relevance 80.91%

I have a formula that references values from a different worksheet. I am instead needing those values to actually be in there! Instead of just being referenced by a formula.

My formula is:
=IFERROR(VLOOKUP($A12,Table2,10,FALSE),"")
Click to expand...
 

Answer:EXCEL - Making values stick in a cell

Select the entire range of cells in question and then right and choose COPY. Then right click again and choose PASTE SPECIAL and then select VALUES as the paste type. FYI you can do this for a single cells, entire columns, or all the cells in the entire sheet at once.

Rollin
 

1 more replies
Relevance 80.04%

Here's an example of similar data:Name Count Column3 Column4A 2 50 51B 1 40 41C 0 1 2D 3 52 55What macro could I create that would produce this data?Name Count Column3 Column4A 2 50 51A 2 50 51B 1 40 41D 3 52 55D 3 52 55D 3 52 55Put into words, what macro could I create that would repeat each row the number of times contained in Column B?

Answer:How can I copy rows based on cell values in Excel?

Sub CopyByNumber()
'Determine last Row with data in Column A
lastRw = Range("A" & Rows.Count).End(xlUp).Row
'Loop throught rows in reverse order
For srcRw = lastRw To 2 Step -1
'Determine how many rows to copy and insert
copyNum = Cells(srcRw, 2) - 1
'If Column B was 0, delete the Row
If copyNum = -1 Then
Rows(srcRw).EntireRow.Delete
End If
'If Column B was not 0, Copy and insert
For copyRw = 1 To copyNum
Rows(srcRw).Copy
Rows(srcRw).Insert
Next
Next
End SubClick Here Before Posting Data or VBA Code ---> How To Post Data or Code.

3 more replies
Relevance 79.75%

Dear Folks,

I have an excel sheet(Original) with 30K+ rows, there is a Header row followed by value row, the whole excel sheet rows are alternating like this.

I need to copy the list of all unique Headers in top row of new sheet(Desired), and all its related values below the corresponding headers.

In short,I am trying to consolidate "Original" sheet in "Desired".
Any help on this is greatly appreciated.
Thanks- Rakesh
 

Answer:Solved: search multiple values and copy below cell to next-sheet

12 more replies
Relevance 79.75%

Hi, I am new here so please excuse any faux pax on my part. I was browsing the web and found a post here on how to delete rows in xls that did not contain a certain word in a specific column. Works great!

I need to specify about 12 different possible words that I want to retain, and delete all rows where the cell does not contain any of those 12 possible words. I tried an or and an elseif statement, but xls did not like it.

Can anyone point me to how to look for a list of words?

The posting for one value was:

Sub delete_rows()
Dim lastrow As Long
Dim row_index As Long
Application.ScreenUpdating = False
lastrow = ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Row
For row_index = lastrow - 1 To 1 Step -1
If InStr(Cells(row_index, "B").Value,"SEOP")=0 then
Cells(row_index, "B").EntireRow.delete
End If
Next
Application.ScreenUpdating = True
End Sub

Thank you!
ann13sch
 

More replies
Relevance 79.75%

Hello all,I am trying to find a way to have all selected values from a drop list (with multiple values selected) to also displayed/copied in a cell (say A25). This so later on you push a button and the values in cell A25 are copied with other values that were entered in other cells to a new row. So eventually the sheet will fill up with rows that display some manually entered values and a cell that contains a sting of text (made up from the values that were selected from the dropdown menue).The list contains names, that i want to be displayed in the A25 cell as text separated by a comma. Exm. Dave, Partic, SimonThank you for your time and effort.Sebastiaan

Answer:Copy Multiple values from Drop list to single cell

How are you selecting multiple values from a Drop Down list?Check out the video at this site then click on the link in the Video Description to download an example spreadsheet that includes the code.http://www.youtube.com/watch?v=0rH8...Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

2 more replies
Relevance 79.17%

So, this one has me stumped and the Internets is not revelatory.

I am trying to create a decision analysis tool in Excel. It's a typical design: one structure for criteria, another for proposals, a third to enumerate the alternatives, and a fourth for scoring.

It's fine for simple decisions where all proposals are mutually exclusive. I'm trying to incorporate the concept of dependency and partial exclusivity, though.

So, for example, on my proposals sheet, I've created a simple exclusion list that looks a bit like so:
Code:

[Proposals Sheet]
C1 C2 C3
=======================================
x1 ...is incompatible with... y3
x2 ...is incompatible with... y2
x3 ...is incompatible with... y2

Where x1, x2, x3, y2, and y3 are all proposals being analyzed.

The alternatives are enumerated automatically using binary math, like so (just a sample extract):
Code:

[Alternatives Sheet]
x1 x2 x3 y1 y2 y3 | Alternative Code
==========================================
0 0 0 0 0 0 | Do nothing
1 0 0 0 1 0 | x1, y2
0 1 1 0 1 0 | x2, x3, y2 ****
0 0 1 1 0 0 | x3, y1
...

The row with all those asterisks is mathematically considered an alternative but should not be considered due to the exclusion list--it includes both x2 and y2, which are incompatible.

I'm trying to find a worksheet formula that will check whether an incompatibility defined on the Proposa... Read more

More replies
Relevance 78.3%

I need a code that will allow the workbook to be emailed when Column A is populated by certian numbers. The numbers in column A corespond to particular email addreses. This is the code I've been working but it isn't functional.

Sub Email_Out()
If Worksheets("Sheet1").Range("A5:A200") = "190030001" Then
ActiveWorkbook.SendMail Recipients:=("[email protected]")
ElseIf Worksheets("Sheet1").Range("A5:A200") = "190450025" Then
ActiveWorkbook.SendMail Recipients:=("[email protected]")
End If
End Sub

All help is greatly appreciated!
Mikey
 

Answer:Solved: VBA email excel workbook based on cell values using; If Then ElseIf Please he

16 more replies
Relevance 78.3%

Hello,

A pretty inexperienced VBA'er here.

I have 2 named ranges:

- LastStatus = "J8:J500"
- CurrentStatus = "K8:K500"

What I would like to do is create a loop to copy the cells in CurrentStatus over to the LastStatus column if and only if there is a value in CurrentStatus (column K). If there is no value in column K and there is a value in column J, I do not want change the value in column J.

Any help would be greatly appreciated.

Brian

 

Answer:Excel - Conditional Copy / Paste (Same Worksheet) based on cell values

You don't need a loop.

Sub test()
Range("LastStatus").SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=IF(RC[1]<>"""",RC[1],"""")"
Range("LastStatus").Value = Range("LastStatus").Value
End Sub
 

1 more replies
Relevance 77.14%

i am uploading excel work book with 4 sheets, sheet no1 contains the record to be verifed/matched with the records in sheet no.2. we will match BTC_Name, BTC_Fname, Deg_RegNO of sheet1 with student name, father name, reg.no. of sheet no.2, if records of sheet no.1 are matched/presented in sheet no.2 then the whole row of sheet no.1 should be copied in to sheet no.3 (if matched display here) else other wise mismatched/ not presented records of sheet no.1 in sheet2 should be displayed (whole row) in sheet no.4 (not matched display here). i have shown sample values in sheet 3 and in sheet 4 taken from sheet 1.
thanx.
 

Answer:compare/match multiple column values in multiple excel sheet

11 more replies
Relevance 76.85%

I want to search multiple values in excel together and want them to be highlighted. E.g. I want to search the following 4 values together with a single command in an excel468.307482.3212482.3219504.34Pls help.ThanksMainak

Answer:Searching multiple values in excel

We need more details...Where are these values? In a single column? Anywhere on a specific sheet? Anywhere on multiple sheets? Etc?Do they all have to be present to be highlighted or if any one or more are found should it/they be highlighted?Are these the actual values or are they just examples? Will there always be four values or will the numbers of searched-for values vary?These are just some of examples of the types of details that we need in order to help us offer a usable solution. The more you tell us about what you are trying to do and how your spreadsheet/workbook are laid out, the better.Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

2 more replies
Relevance 76.85%

I have 3 lists, a master locker register, an employee list, and a resignee list.I have to update the master locker register referring to the resignee list. below is the example of a value in the master locker registerfloor | locker number |staff name| *employee ID*| process | line mgr| extension|2 s345 adam 43614985 SS mada 2589the resignation list is basically the same thing but the thing is someone else is editing it and i find it difficult that the person editing it puts in values all across (according to date) instead of just adding on to the list (which is much easier as i will be able to track the changes)i am also doing this very annoying manual cut and paste method. the value that i need to compare between the 2 is the employee ID. please help

Answer:Searching for multiple values in excel

First, please repost your data after reading the How To referenced in my signature line.Second, keep in mind that we can't see your spreadsheet from where we're sitting, so we have no idea what this means:"the person editing it puts in values all across (according to date) instead of just adding on to the list"Please explain, or better yet, post an example - after reading the How To.Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

2 more replies
Relevance 76.85%

Hello,I have an Excel spreadsheet with a column of hyperlinks to all of the photos on my hard drive. In this spreadsheet I have a column for key tags for each photo. I want to be able to search the key tags and isolate each row that contains the key tags, hyperlinks and additional data in the spreadsheet. Here is an example of the key tags column Events Baby Shower Lunch Feb 09 Kim's Baby ShowerRight now if I do the basic CRTL F search and I type in "Events Lunch" it will not find the above entry. Is there a Macro I can set up to continuously use that would put each matching entry in a new spreadsheet? Also I would like to be able to search for words that are not on the spreadsheet and produce a search with results, for example if I searched "Books Baby Shower" and no key tags in the entire spreadsheet had "Books" I would like to still see everything with "Baby" and "Shower" in the key tags column.any tips or better ideas would be greatly appreciated,Thanks.

Answer:Find multiple values in Excel

Instead of searching for Events Lunch, you can find the above entry with:Events * Lunchas long as the "Match entire cell contents" box is not checked.Instead of searching for Books Baby Shower you can find the above entry with:b? * showeras long as the "Match entire cell contents" box is not checked.Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

3 more replies
Relevance 75.98%

I have similar kind of Excel report extracted from remedy tool having around 400-500 rows of records. My search string have list of 100's of server names. I want to find out the records having servers from search string. The required search operation would be on two columns.

Answer:How to search multiple list values from excel

It is not clear to me what you are trying to do.Please click on the blue line at the end of this post and read the instructions on how to format and post example data in this forum. Then post a short example of your data and your search string. Don't forget to use Column letters and Row numbers as shown in the example. Thanks!Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

2 more replies
Relevance 75.98%

Hi,

I need the help of a guru. I'm an intermediate (NOT ADVANCED) user tasked with an assignment and I'm not sure of the best way to execute the idea. I have two spreadsheets FILLED with data. I have to take Manufacturer numbers from a column in one spreadsheet, and compare them to the the manufacturer numbers in a similar column of another spreadsheet to find matches. There are thousands of numbers! What is the best and quickest way to do this?! I would love to open List B and run a check for all the numbers from List A at the same time. Hope I'm making sense because I need a savior.

Please answer my question (in a user-friendly, step by step fashion if you would) and consider your good deed for the day done!

Thanks,

*Overwhelmed*
 

Answer:Excel - Search Multiple Values at Once (Overwhelmed)

Welcome to TSG dusk.

Look at the Vlookup function.
 

2 more replies
Relevance 75.98%

HiI am really badly stuck at this and am a rookie on excel. I am building portfolios based on Market values(MV) and then multiplying those MV's with returns calculated from Price Index (PI). Iv got approx 300 companies, out of which I have to make 6 portfolios for each year comprising of 20-30 companies out of those 300. The company names are in in the top row and the MV values follow in columns. same happens with the returns which is, as i said, function of the PI values, on a different sheet. So now i need to search for those portfolio companies one by one and then copy paste the MV values first and then repeat the whole process to get the return values from PI sheet, then multiply them together and find the total portfolio return. I have uploaded the file on zippyshare http://www66.zippyshare.com/v/35938...In this file you will see 2004 portfolio where i have actually made the first "BH" portfolio by doing what iv mentioned, when ull scroll down ull see another portfolio "BM" where the companies are listed. Is there an easier way to search through the 300 companies to retain the required companies and their corresponding yearly values and filter out the rest? and same could be done for returns?THANKS a million in advance

Answer:Finding multiple data values in excel

I'm not if this is what you are looking, so let me know...In 2004 Portfolio!B2 you have a value of 6013.42 which I assume came from MV 2004-2005!KF2.This formula will return that value from MV 2004-2005 using the INDEX and MATCH functions.The first thing I did was turn MV 2004-2005 into a Named Range (MyData). I then used the following formula to return 6013.42 into 2004 Portfolio!B2The MATCH functions will return the row_num of the value in 2004 Portfolio!A2 and column_num of the value in 2004 Portfolio!B1 based on where they are found in MyData (MV 2004-2005).It will then use those row_num and column_num values in the INDEX function to return the value found at that intersection.=INDEX(MyData,MATCH(A2,'MV 2004-2005'!A:A,0),MATCH(B1,'MV 2004-2005'!1:1,0))Hope that helps!Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

2 more replies
Relevance 75.98%

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
 

Answer:Solved: Comparing multiple values in excel

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

1 more replies
Relevance 75.98%

Not an expert so I need allot of help. In a production schedule I have various models to make with X number of units per model in a day. Using VLOOKUP I have a time value (production time) column for each model multiplied by its number of units, so I know the time it takes to produce each individual model in a day. I now need to include a PST (Production Standard Time) column for each model so workers can know at what time they should finish each of the models in the daily schedule. During our work hours (7:00am - 5:06pm) I have 3 lunch/rest breaks one @ 9:20 am(lasts 19min), 12:30 pm(lasts 29min) and 3:00 pm(lasts 9min) so I need to add to the PST that specific lunch/rest time if a model is finished during any of the lunch/rest periods, so I?ll assure the PST is accurate. How can achieve this using one single formula, considering the 3 criteria?

Criteria #1: IF((A1+B1)>=timevalue("9:20 am") but <=timevalue("12:30 pm"), (A1+B1)+time(0,19,0), (A1+B1))
Criteria #2: IF((A1+B1)>=timevalue("12:30 pm") but <=timevalue("3:00 pm"), (A1+B1)+time(0,29,0), (A1+B1))
Criteria #3: IF((A1+B1)>=timevalue("3:00 pm"), (A1+B1)+time(0,9,0), (A1+B1))

Since the values will be adding up as the day goes, it can occur, that the value (A1+B1) falls directly into the second criteria (eg. 1:37 pm), I need then for it to add the 29 minutes plus the 19 of the first criteria, and so on and so forth. Thanks so much in advance.
HEL
... Read more

More replies
Relevance 75.98%

I have an excel spreadsheet with multiple lines of data; and within one column I need it to find every line that has a specific word in it and move that row to another tab. I have multiple words to seach for. The below code is close, but it stops after it finds the first instance of the word in the list. What I would actually like it to do, instead of copying to another sheet is color that cell a particular color. Any assistance would be appreciated.
Sub GeneFinder()
Dim srchLen, gName, nxtRw As Integer
Dim g As Range
'Clear Sheet 2 and Copy Column Headings
Sheets(2).Cells.ClearContents
Sheets(1).Rows(1).Copy Destination:=Sheets(2).Rows(1)
'Determine length of Search Column from Sheet3
srchLen = Sheets(3).Range("A" & Rows.Count).End(xlUp).Row
'Loop through list in Sheet3, Column A. As each value is
'found in Sheet1, Column D, copy it top the next row in Sheet2
With Sheets(1).Columns("D")
For gName = 2 To srchLen
Set g = .Find(Sheets(3).Range("A" & gName), lookat:=xlWhole)
If Not g Is Nothing Then
nxtRw = Sheets(2).Range("D" & Rows.Count).End(xlUp).Row + 1
g.EntireRow.Copy Destination:=Sheets(2).Range("A" & nxtRw)
End If
Next
End With
End Submessage edited by Mloftin66

Answer:Find Multiple Words/Values In ExcEL

Does anyone have an answer for the above?

2 more replies
Relevance 75.98%

I run a file room and have 55,000 files listed in an excel sheet by rack number / shelf numberRACK FILE1 FILE2SHELF 2.2.1 00-DCV-115533 TP 01-DCV-121638SHELF 2.2.2 07-dcv-156148 07-DCV-156329 TPSHELF 2.2.3 08-DCV-161660 tp 08-CV-161663SHELF 2.3.1 08-DCV-166310 08-DCV-166316 TPSHELF 2.3.2 09-DCV-169134 09-DCV-169166When users request files (by using an excel sheet) I would like to be able to click a 'find' button and use values from the user provided excel sheet to find those files in the file room excel sheet.Here is a sample file sent by a user:FILE NUMBER REQUESTOR'S NAME09-DCV-169193 Erronda Harris 09-DCV-171620 Erronda Harris 09-DCV-173211 Erronda Harris

Answer:Search excel file for multiple values

A database may be a better tool.

6 more replies
Relevance 75.98%

Hello I have long string that looks like that:"54665 - Name 6420, 5412, 5224, 4454, 4408 - SSL Acceleration - Upgrade from 500 CPS to 2,000 SSL CPS; 9210027 - Upgrade from 500 CPS to 2,000 "I wont to search for exist value 9210027 inside the string and copy it to anither column.Any function to do it ?Thank you!

Answer:Find multiple values in Excel string

Assuming that the parts numbers that are embedded in the string are always preceded by a "semicolon space"then try this:With your data in cell A1do a Text To Columns to split the string at the semi-coloninto three seperate cellsThis is for 2007:Highlight cell A1On the ribbon select the DATA tabSelect Text to Columnsselect the Delimited buttonClick NextFrom the list of Delimiters, check SemicolonClick NextClick FinishYou should now have three cells, A1, B1, C1with your seperated data and each cell should begin with your part number.Now in cell D1 enter the formula:=LEFT(A1,FIND(" ",TRIM(A1),1))&", "&LEFT(B1,FIND(" ",TRIM(B1),1))&", "&LEFT(C1,FIND(" ",TRIM(C1),1))In essence it is the same formula repeated three times and concatenates the three cells A1, B1, C1 together seperatedby a comma.See how that works for you.MIKEhttp://www.skeptic.com/

8 more replies
Relevance 75.98%

Is it possible to build several if statements that return multiple values if satisified to one cell in excel. For instance, I have the following Info in Excel:Cell A1: Coborrower Cell B1: John DoeCell A2: Principal Cell B2: Jane DoeCell A3: Coborrower Cell B3: John SmithCell A4: Coborrower Cell B4: Jane SmithCell A5: Guarantor Cell B5: Jack DoeIn Cell G6, I want to create an if statement that says if coborrower is found in cell A1, A2, A3, A4, or A5 then return the corresponding values of the B Column seperated by commas. For instance, in the scenario above I would want G6 to read: John Doe, John Smith, Jane Smith

Answer:If Statements Returning Multiple Values Excel

The result listed above does not have to be seperated by commas but would be preferred.

5 more replies
Relevance 75.98%

Hello.
I have a document with 4 columns, in column one there are codes, in column two there are names and in columns 3 and 4 are start and end dates.
There can be multiple codes for each name.
What I'd like to do is create a second worksheet with the names listed (once each) in column A, and have the corresponding codes and dates in the adjeacent cells, so I'd go from this:

Code____Name____Date1____Date 2
A1______ABC123___01/01/05__20/01/05
A2______ABC123___15/01/05__12/02/05
A1______DEF456___01/01/05__08/01/05
A5______DEF456___07/02/05__10/02/05
To this:

Name_____Code1___Date1____Date2____Code2___Date1_____Date2
ABC123_____A1_____01/01/05___20/01/05_____A2____15/01/05___12/02/05
DEF456_____A1_____01/01/05___08/01/05_____A5____07/02/05___10/02/05
A name may have between 0 and possibly 10 codes.
Is there a way to do this?
I've looked at VLOOKUPs, Pivot tables and Row Indexes, but can't seem to get it to work.

Any help is appreciated.

Gram
 

Answer:Excel 2002: Lookup multiple values

i think a pivot may do it but not maybe give you the order you have more a grouping
 

2 more replies
Relevance 75.98%

Hi,Office 2010 Excel I have a table with code reference and value next to it I want to sum all the values with the same code reference.example:2 1003 702 202 200 5 1083 233The result should be: 100+20+200 = 320 thanks for the answers.

Answer:excel formulas lookup sum multiple values

Look up the SUMIF function in the Excel Help files.If you have any trouble figuring out how to use SUMIF, come on back and we'll see what else we can do to help you.Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

2 more replies
Relevance 75.98%

Hello,What I would like to do is to use the for example the Ctrl-F function in Excel to look for different words/values at once. Is this possible?Even better would be a macro that searches a certain worksheet for lets say 10-100 predefined words/values. If the word/value is found, the row in which the word/value appears should be copied to a second worksheet.Thanks in advance,E.

Answer:Find multiple words/values in Excel

I suggest that you try the code in a backup copy of your workbook since macros can not be undone.1 - Press Alt-F11 to open the VBA Editor2 - Click on the name of your workbook in the left hand column3 - Click on Insert...Module4 - Paste the code into the right hand pane that opens5 - You should see Blue, Green and Black text. If you see any Red, something's not right. Post back here describing what's Red.6 - To Run the code, click anywhere in the code to place the cursor in the code, then click the Green arrow in the tool bar.The code will either do what it is supposed to do or throw up an error. Post back with info on the error if that happens. How long it takes to run will depend how on much data it has to search through. When the cursor starts blinking in the code, it's done.You can also Single Step through the code by pressing F8. Each time you press F8, one line of code will be executed. This can help narrow down problems if they occur. You can single step for awhile and then click the Run arrow once you're satisfied that the code is doing what you want.In single step mode, you can switch back and forth between the workbook and the VBA Editor to see if lines are being copied. You can even size the VBA window so that you can see your workbook behind it to see if lines are being copied as expected.You can assign the macro to a button, a function key or a menu item to make it easier to run once you are sure it is working. Google something like assign macro to button for var... Read more

20 more replies
Relevance 75.4%

Hello,I have a list of about 4,000 lines of Reference codes that need to be looked up at once from a separate sheet and then highlighted. I have this code currently Option ExplicitSub DocFinder()Dim srchLen, DocName As IntegerDim g As Range'Determine length of Search Column from Sheet3 srchLen = Sheets(3).Range("A" & Rows.Count).End(xlUp).Row'Loop through list in Sheet3, Column A. As each value is'found in Sheet1, Column E, Highlight it With Sheets(1).Columns("E") For DocName = 1 To srchLen Set g = .Find(Sheets(3).Range("A" & DocName), lookat:=xlWhole) If Not g Is Nothing Then g.Interior.ColorIndex = 5 End If Next End WithEnd SubIf it looks familiar it is because I had modified it from a similar post. My problem is that it only finds the first hits and highlights it. I have many repeated reference codes that all need to be highlighted. I have tried implementing a for each but the program will then cease to run.Any help will do.Thanksmessage edited by JonaRey

Answer:Find multiple words/ values in excel and highlight them

Just for future reference, it would be easier for us to help you if you would post your complete macro, not just a small portion. When we want to test your code, we want to be able to Copy it directly into the VBA editor and run it. When you only post a section, we have to '"rebuild" it to get it to run. If you make us do too much work, we might get bored and walk away. ;-)As for your error, think about what g is. g is the object that gets set when the .Find method finds what it is searching for.As I explained in my previous post, your If statement is testing to see if g is Not Nothing. Only when g Is Not Nothing does it have properties such as .Row, .Column, .Value, etc.The code will only fall through to the Else section if g is Nothing and if g Is Nothing you can't reference its Row, you can't copy it, you can't do anything with it. After all, it's Nothing, and just like in real life, you can't do anything with Nothing. ;-)OK, so why was g set to Nothing? Because the current search string wasn't found. What is it that you are trying to copy? The unfound search string, right? So, don't try to copy g, copy the current search string which wasn't found.I'll be nice and tell you upfront that even if you try to copy your current search string, you are still going to get an error. Range(?A?) is not a valid destination Range. You have only referenced a Column without a Row. VBA will not like that. You also need to keep in mind that you have to increment that row each time you ... Read more

14 more replies
Relevance 75.4%

I want to Highlight Multiple Values with an Excel Macro. I want the macro to loop though and grab the list/range of values from another sheet and use it in the Array.

I want to avoid having to enter such a big list of values for the Array. I want to use something like "SearchVal() = Range("Lists!A2:A62").Value" but I get an "out of range" error. Here is my code now.

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Sub Highlight()
Dim SearchVal() As Variant
SearchVal() = Array("ALM", "AXV", "AZZ", "BCT", "BHN", "CRE", "CSI", "CVM", "DCH", "DEK", "DER", "ESE", "GCV", "HCO", "HND", "HSI",
"IBO", "ILW", "KCM", "LEO", "LES", "LPA", "MCY", "MEX", "MHS", "MRM", "MSL", "NCL", "NTX", "NZI", "PHC", "PLA", "PPH", "PSE", "PUV",
"PVN", "PWL", "RKC", "RSN", "SCM", "SEL", "SGA", "SHC", "SRG", "SVL", "SYV", "TGC", "THA", "TSL", "VAV", "VID", "VTA", "WHE", "WHH",
"WIH", "WWJ&qu... Read more

Answer:Solved: Highlight Multiple Values with an Excel Macro

8 more replies
Relevance 75.4%

Hi I am currently using the code below to find multiple values and the copy/paste into another sheet. What I would like to know is if it is possible to add the now date to a column in sheet 1 for the value found in sheet 3?Option ExplicitSub BBIB()Dim srchLen, gName, nxtRw As IntegerDim g As Range'Clear Sheet 2 and Copy Column Headings Sheets(2).Cells.ClearContents Sheets(1).Rows(1).Copy Destination:=Sheets(2).Rows(1)'Determine length of Search Column from Sheet3 srchLen = Sheets(3).Range("A" & Rows.Count).End(xlUp).Row'Loop through list in Sheet3, Column A. As each value is'found in Sheet1, Column A, copy it top the next row in Sheet2 With Sheets(1).Columns("A") For gName = 2 To srchLen Set g = .Find(Sheets(3).Range("A" & gName), lookat:=xlWhole) If Not g Is Nothing Then nxtRw = Sheets(2).Range("A" & Rows.Count).End(xlUp).Row + 1 g.EntireRow.Copy Destination:=Sheets(2).Range("A" & nxtRw) End If Next End WithEnd SubAny help much appreciated.

Answer:Find multiple words/values in Excel and add date

Hi!Please click on the blue line at the end of this post and read the instructions on how to post VBA code in this forum. Then please repost your code, as copied from the VBE, so that it is easier for us to read.Thanks!Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

4 more replies
Relevance 75.4%

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,
 

Answer:Solved: Excel - How to return a multiple of matching values?

6 more replies
Relevance 75.4%

Hi,
This is probably a very simple macro but programming isn't my strong point.
I need an Excel Macro that will be able to scan the first column of a worksheet row by row for certain document numbers i.e. "09-005", "09-1052", "586463" and highlight them. There is a substantial list of these.
I managed to figure out that I need a range and how to highlight, but am getting stuck on how to find the multiple document number values.
Any and all help is greatly appreciated
Thanks in advance
 

Answer:Solved: Highlight Multiple Values Excel Macro?

8 more replies
Relevance 105.37%

Hi,
I have frequently used an array formula like this to look up multiple records in a list in Excel:
{=INDEX($A$1:$B$7),SMALL(IF($A$1:$A$7=$A$10,ROW($A$1:$A$7)),ROW(1:1)),2)}
It nicely retrieves all the rows where A10 is the value to be looked up.

However I have not had any success using the same type of formula with an OR statement:
{=INDEX($A$1:$B$7),SMALL(IF(OR($A$1:$A$7=$A$10,$A$1:$A$7=$A$11)),ROW($A$1:$A$7),ROW(1:1)),2)}

I want to retrieve all the records in a list where the value is what is as in A10 OR A11.

Does anyone know how I can achieve this?
Thanks!!!!!
Janie
 

Answer:Excel - looking up multiple records of multiple values

7 more replies
Relevance 105.37%

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 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 "Part" 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 "Full" to blank cell in column D

I have a massive list of 3800 names that gets 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 to my existing data. I need an easy ... Read more

Answer: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
 

3 more replies
Relevance 104.14%

Hello,
I've searched and have not been successful to find an answer to my issue.
I have a workbook with multiple spreadsheets. Each spreadsheet has a list of employess with tasks and hours.
I need a macro that will search for the employee name and add the hours spent working on a task.
I also need to search for the different tasks and add the total hours spent on the specific task.
Workbook contains multiple tabs (Summary, week1, week2, week3, etc)
Each worksheet has three columns and employee name may be in the first column multiple times with different tasks:
Employee Tasks Hours
Jason Dyer dishes 8
Joyce Brown lawn 12
Donald Steiner vacuum 16
Jacqueline Lowe dusting 4
Robert Jones mopping 2
Barbara Fritts trash 8
Lisa Stillman painting 10
Antionette Adkisson repair 6
Billy Barkley errands 14
Ian Grayson sweeping 18

And the Summary has:
Employee Total Hours
Jason Dyer
Joyce Brown
Donald Steiner
Jacqueline Lowe
Robert Jones
Barbara Fritts
Lisa Stillman
Antionette Adkisson
Billy Barkley
Ian Grayson
Task Total Hours
dishes
lawn
vacuum
dusting
mopping
trash
painting
repair
errands
sweeping

Any help will be appreciated!
 

Answer:Excel 2007 search and match macro, add values in multiple spreadsheets

7 more replies
Relevance 104.14%

Hi all,

This may be more complex than I think but I have searched hundreds of forum posts all over the place and while I've come close to finding a solution to this; nothing has quite described a way to do this...

Basically, I have a roster for staff (attached is a simple sample I've thrown together to show you what's on the rows / headers & sample contents...obviously the actual spreadsheet is much bigger and month-to-view on each sheet).

Let's assume the following:
- sheet 1 and sheet 2 have staff rosters on as per the example spreadsheet
- sheet 3 is where I want to display the search results
- the same name may/will be present on different dates and different shifts
- this is for me and not an 'end user' so it doesn't have to be in any way flash in its working or pretty!!!

I would like a search entry box on sheet 3 (let's say in cell A1).

I need the following results returned for EACH occurrence of the searched name (this is where it gets beyond me). These will be copied into sheet 3 let's say starting from cell A3 - I'll stick in a clearcontents on the range at the start of the sub as the results will be copied into an email and then can be cleared when I need a new search:

- Shift (always found in Column A of the sheet being searched)
- Job Number (always found in in Column B of the sheet being searched)
- Date of shift (always found in Row 1 of the sheet being searched)

So essentially it's a lookup / fin... Read more

Answer:VBA Search Excel Workbook - Multiple Values, Return Row & Column Header

16 more replies
Relevance 102.91%

G'day,

I have a Microsoft Excel 2003 file that contains two spread-sheets. In one, I have a list of clients and a column next to each client name that I want to display the amount of money made from that particular client. Then, on the second spread-sheet I have a list of all credit and debits relating to the various clients. So, we might have received $100.00 from a client (and that would be in the credit column next to the client name), and then we may have spent $50.00 on that client (and that would be in the debit column next to the client name). So, obviously, the total amount made from that client would reflect the credit minus the debit.

Anyway, what I need is for the cell on the first spread-sheet that says the total made for that client to look at the second spread-sheet, look for any row that has that client's name and then adds the credits and subtracts the debits, then leaving the total back on the first spread-sheet.

I hope that makes sense. I have attached a demonstration to help, and I have also done the formula for the first client to show you what outcome I am looking for (although the formula is not what I want because it doesn't automatically add all of the rows from the second spread-sheet that share that client name).

Anyway, I hope I've explained it well enough and if anyone can help me I'd really appreciate it!
 

Answer:Solved: Adding Selected Values Together Over Multiple Work-Sheets In Microsoft Excel

hi blujein,

Attached is a quick solution.

Copy the formula I have added to the Total Amounts spreadsheet in Col B, down in new rows as you add new clients.

lol
Hew
 

3 more replies
Relevance 100.45%

I need help on this.
I have a minimum of 50 numbers in one cell. What I want is split those numbers into 50 different cells.

Ex: A1 would be 21364790461234585960485938475811293849388223344123

I need to split the numbers into multiple cells; 1 number in 1 cell.
So in A2 it should be 2
In A3 it should be 1
In A4 it should be 3
In A5 it should be 6
In A6 it should be 4
And so on....
 

Answer:Split numbers in one cell into multiple cells in excel

you could use the mid function

so assuming in cell A1

A2 = Mid(A1,1,1)
A3=Mid(A1,2,1)
But you would need to type that in
a better way is to use the row number to get the start position and then you can copy
ROW(A2)-1 to get the start position - if in a2 will return row as 2 , -1 = 1
and can be copied down

A2 = Mid($A$1, ROW(A2)-1, 1)
and just copy to row 51

see attached
 

3 more replies
Relevance 100.45%

Hello all,I have two questions, could you please help me:1. I have many cells with values like that:25pq568925tyu568925ter568925QD5689I need to change letters with numbers (every letter correspond to appropriate number like a=02, b=05, etc...), and I expect to have in results:2505645692556456892501256892558956892. Next one, I have two columns:| number | name || 29 | N1 || 56 | N1 || 45 | N1 || 58 | N2 || 89 | N2 || 55 | N3 || 65 | N3 || 47 | N3 || 56 | N4 || 10 | N4 |I need to SUM values from column "number" for N1, N2... from column "name".

Answer:Sum of values by criteria from other cells

I'm a tad confused by your examples. Some of the "input" values have 2 letters and some have 3, yet your outputs don't seem to match those configurations.Were those supposed to be one-for-one inputs/outputs?Is this a 1 time thing or will you have to repeat this conversion on a regualr basis?As far as your second question, review the Help files for the SUMIF function.Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

3 more replies
Relevance 100.45%

I have multiple sheets (12 months) in my workbook. I am on the sheet "February Earning 2010" (third page in my workbook) and I am in cell P8.I need a total amount to show in this cell for all of the previous earnings on the prior two sheets. So I need it to refer to cell C8 because that is where the contract number is located. Then I need it to look for the contract number on:"December Earnings 2009" and "January Earnings 2010". On each sheet, the contract numbers are listed in column C. The values to be added up is where are get really confused.The values could be in Q8, S8, U8, V8 or W8 of the row.Each time the contract number (in row C of each page) is found, I want to add the amounts in the cells (Q8, S8, U8, V8 or W8). What formula should I use for this?

Answer:Sum of values in various cells if criteria me

Hi,Two questions:1. Do you only add data from one row on the preceding worksheets - i.e. is there only one row with the required contract number (and it's amounts) or do you need to add up the results from several rows, each containing the required contract number.2. If you have to get data from columns Q, S, U, V or W, if the value required was in column S (say S8) are Q8, U8, V8 and W8 empty. If not how can you decide which column to use.Regards

12 more replies
Relevance 99.22%

Hello

I am working on a comparison macro that takes information from my system and compares it to information from a different system. The problem lies in the format the other system comes in. I have been able to clean it up as much as possible but i just can't figure out the last little step.

If I have 15 containers on a an order and I am missing all 15 I need 14 rows inserted and all the information copied from the first line entered into the other 14. I thought i had this down but what i didn't take into consideration was that there are times when i have an order for 1 and missing 1 if that is the case i don’t need anything done. And then there are times when i have a an order for 15 i have 7 and it gives the names of the containers so i need 8 lines entered and this time when the information is copied i don’t need the container names it has to be blank.

I am going to attach a spread sheet of what i am talking about. The first tab is what it looks like when it comes in and the second tab is what i need it to look like. I color coded it to make it somewhat easier to understand. The rows in red are the original data and the rows in yellow are the ones i need added. I have asked this on several different forums and have not received a response at all hopefully someone here can help me with this. if you need more information i will gladly provide.
 

Answer:Inserting Rows for values in certain cells

6 more replies
Relevance 99.22%

If I have an EXCEL which has:Column A mentioning illumination status as FL or BL or NLColumn B has sq ft areaI want that Column C should automatically give the value as mentioned below:If A=FL, then value in C should be B multiply by 10If A=BL, then value in C should be B multiply by 25If A=NL, then value in C should be B multiply by 10

Answer:Value Calculation based on values in other cells

Here is a formula:=IF(A1="FL",B1*10,IF(A1="BL",B1*25,IF(A1="NL",B1*10)))if A1 = FL it will multiply value in C by 10if A1 = BL it will multiply value in C by 25if A1 = NL it will multiply value in C by 10

4 more replies
Relevance 99.22%

For an assignment I have to create a spreadsheet with columns that have given values. I am lost on how to assign a word a value without using to separate cells. Here is part of the assignment as an example:Constants needs the following fields: Target Markup % ( give this the value of 102%), Target Price ( give this the value of $4.00 ) Base Costs needs the following fields: Qty on Hand (a numeric input field; the sum of the Warehouse On Hand), Cost Per Unit (a currency input field; the average of the warehouse Costs per Unit), Inventory Total Cost ( calculated as Qty On Hand times Cost Per Unit )

Answer:How to give values to cells with wording

Why don't you want to use separate cells?It reads to me that the "words" you are concerned with are nothing more than Column headings.The only other thing I can think of is possibly Named Ranges. Look that up in the Excel Help files or DAGS. Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

3 more replies
Relevance 99.22%

I need to add many individual cells together that are formatted as iferror, return "-". I do not want to change the "-" to 0 because i don't want to see a thousand 0's, but that is the only way i can get the value in the cell i want

Answer:adding cells with text values

Perhaps it would help if you posted the formula you are using so that we can get a better idea of your issue.If you are trying to count text values, perhaps COUNTIF or COUNTA will get you what you are looking for.Bottom line is that I don't think we have enough information to give you a concrete answer.Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

2 more replies
Relevance 98.4%

Hi,I have the following code which works, but I want to also copy "T" and "U" as well. Is there a way to modify the code below to do this?Sub CopyLaborHrs()
Dim i, LastRow

LastRow = Sheets("Component Labor Summary").Range("A" & Rows.Count).End(xlUp).Row
Sheets("Labor Hr Breakdown").Range("A2:V500").ClearContents
For i = 2 To LastRow
If Sheets("Component Labor Summary").Cells(i, "U").Value > "0.00" Then
Sheets("Component Labor Summary").Cells(i, "A").Copy _

Sheets("Labor Hr Breakdown").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
End If
Next i

End Sub
Thank you Sandi

Answer:Copy Multiple Cells instead of Single Cell Excel Code Update

First, a posting tip: Please click on the blue line at the end of this post and read the instructions on how to use the pre tags to post VBA code in this forum so that it is easier for us to read. As far as your question, I assume that you want the values in Columns T & U copied to Columns T & U on the Labor Hr Breakdown sheet.I have posted the modified code below, but I would like to suggest that you review the material in this tutorial so that you can learn how to make these modifications on your own. We are more than willing to help, but the more you understand about VBA code, the easier it will be for you - and us. ;-)http://www.computing.net/howtos/sho...
Sub CopyLaborHrs()
Dim i, LastRow, dstRw
'Determine last row with data in Column A of Component Labor Summary sheet
LastRow = Sheets("Component Labor Summary").Range("A" & Rows.Count).End(xlUp).Row
'Clear range on Labor Hr Breakdown sheet
Sheets("Labor Hr Breakdown").Range("A2:V500").ClearContents
'Loop through Column U of Component Labor Summary, copying
'data to Labor Hr Breakdown sheet if U value > 0
For i = 2 To LastRow
If Sheets("Component Labor Summary").Cells(i, "U").Value > "0.00" Then
'Determine next empty row in Labor Hr Breakdown sheet
dstRw = Sheets("Labor Hr Breakdown").Range("A" & Rows.Count).End(xlUp).Row + 1
'Copy data from Column A, T & U
Sheets("Component Labor Summary").Cells(i, "A").Copy
Sheets("Labor Hr Breakdown").Range("A" & dstRw).PasteSpecial xl... Read more

3 more replies
Relevance 97.99%

hi
i have a sheet with a few listboxes. when selecting an item from a listbox it populates various cells. that works well using vlookup. but now i want to have the same cells populated when selecting a different listbox. so in essence the values from listbox A gets cleared first when selecting an item from listbox B.
any ideas will help thank you.
 

More replies
Relevance 97.99%

I am making an inventory spreadsheet and want the current inventory cell to go red when it goes below the min quantity(in another cell). I use conditional formating to change it red but don't know how to use it for the entire column as it just compares it against the one cell. I want it to compare it to each of their corresponding row.Any help would be appreciated

Answer:Changing color of cells comparing values

With your minimum quantity values in A1:A5 and your actual quantities in B1:B5,Select B1:B5 and use this as the formula for Conditional Formatting:=IF(B1<A1,TRUE,FALSE)It will update for each cell, becoming=IF(B2<A2,TRUE,FALSE), etc.Just make sure you don't use any $ which will lock in the Row numbers.

2 more replies
Relevance 97.99%

how do i get excel to show 325,987,654 as 325million

Answer:I want to get excelt to show the values in cells in millions

You can do it with a formula, but it is not a very good option.I would advise using a Macro.Here is a formula that should give you what your looking for:This formula expects your number to be a regular dollar amount WITH decimal places, IE 1001.12and using zero zero, IE 1001.00 does not work because Excel will strip off the trailing zeros, so you end up with 1001=IF(LEN(A1)>=10,LEFT(A1,LEN(A1)-9)&"Million",IF(OR(LEN(A1)=9,LEN(A1)=8,LEN(A1)=7),LEFT(A1,LEN(A1)-6)&"Thousand",A1))If your numbers NEVER have decimal places, you can use this formula:=IF(LEN(A1)>=7,LEFT(A1,LEN(A1)-6)&"Million",IF(or(LEN(A1)=6,len(a1)=5,len(a1)=4),LEFT(A1,LEN(A1)-3)&"Thousand",))But I would really advise getting a Macro, there are several out on the web.MIKEhttp://www.skeptic.com/

2 more replies
Relevance 97.99%

I need to concatenate a range of cells (approx. 50) across a row (for many rows in my spreadsheet) and before each cell value, insert the cell's header row value. The header value and cell value needs to be separated by [:]Each header/value pair needs to be separated by [;]Example: Color[:]Red[;]Size[:]Large[;]Length[:]LongVB preferred. Many thanks!

Answer:Concatenate range of cells with header values

Without a more detailed explanation of how your sheet is laid out there's no way for anyone to write any code that will do what you ask. We don't know which Rows/Columns to reference, how many cells you need to concatenate, etc.If you are going to post an example of your data, please click on the following line and read the instructions found via that link.Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

2 more replies
Relevance 97.99%

hi thereI have the same problem with positive and exact negative value in a column which should be delete.for example consider the below sample:Column A123-123554-333-554145-145234I just want the below result:Column A-333234as you see just the values with no negative one, remained.how can I write a macro to do this for me?would you please help

Answer:delete rows with cells in a column with th same +and- values

I'm sorry I did not notice the instructions...I really appreciate your answer.you are all right. I forgot to change the D to B. in my real data the D column play the B role in this example.any way...I tested the below code and fortunately. it worked out...Sub Delete_Stuff()

Dim lastRow As Long, myRow As Long, findRange As Range, findRange1 As Long, goalrow As Long, mynewrow As Long
Range("A1").CurrentRegion.Sort Key1:=Range("A1"), Order1:=xlDescending, Header:=xlGuess
lastRow = Cells(Rows.Count, 1).End(xlUp).Row

For myRow = lastRow To 2 Step -1

If Cells(myRow, 1) < 0 Then

Set findRange = Range("A1:A" & lastRow).Find(Abs(Cells(myRow, 1)), LookIn:=xlValues, LookAt:=xlWhole)


If Not findRange Is Nothing Then

goalrow = findRange.Row
For mynewrow = goalrow To (myRow - 1) Step 1
If (Cells(mynewrow, 4) = Cells(myRow, 4)) Then

Rows(myRow).Delete
Rows(mynewrow).Delete
Exit For
End If
Next mynewrow
End If

End If

Next myRow

End Sub
Again thank you very much

6 more replies
Relevance 97.99%

Hi,Been messing around with this for a while, and other than having many nested IF functions, I can not seem to make this easier. If any one has some ideas, I would be happy to be directed the right way. What I have is as followsCells B5, F5, J5, and N5 are the cells that I require to total the 3 highest valuesThe cells between these have values in them as well, otherwise I would use the array B5:N5If B5 = 5, F5 = 8, J5 = 2 N5 = 3 then my total would be 16If B6 = 4, F6 = 3, J6 = 2 N6 = 3 then my total would be 10If B7 = 4, F7 = 4, J7 = 4 N7 = 4 then my total would be 12If I were using an array then the formula would be as follows=SUM(LARGE(B5:N5,{1,2,3}))So from this I need to change the format from an array to a list of particular cells within the rowmessage edited by tonygibb

Answer:Total three largest values from a selection of cells

have you tried this:=SUM(LARGE((B5,F5,J5,N5),{1,2,3}))Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

3 more replies
Relevance 97.99%

Hi,

I have been trying to figure out if it is possible to use a VBA function to place values and formulas into excel cells. In the past, I have done so using Macros, but for my particular application, I want to be able to do so from within a VBA function. Any ideas on how to do this?
 

Answer:Using a VBA function to place values or formulas into cells

Hi
This might give you some ideas.
Sub FormulaInCells()
Dim MyRange As Range
Dim Total As Integer

'Setup Range Pointer
Set MyRange = ActiveSheet.Range("b1:b10")

'Placing Formula's in Range
With MyRange
.Formula = "=int(rand()*10)"
End With

'Using Excel Functions on a Range
MsgBox ("Sum Data & Autofit")
Total = Application.Sum(MyRange)
Range("a11").Value = "Total"
Range("b11").Value = Total
Range("b1:b11").Columns.AutoFit

'Restore Worksheet
MsgBox "Restore Worksheet"
RestoreWorksheet
End Sub
 

1 more replies
Relevance 97.99%

I want to add up the values in individual cells in a large spreadsheet. The cells are dotted randomly across this spreadsheet and not in rows or columns. Is there an auto sum route to this addition
thanks Peter

Answer:adding values in individual cells inExcel

You will have to select each cell one by one.

1 more replies
Relevance 97.99%

i have this code to copy the values from a cell in column G in column C. but i have a problem because this code copy the formulas from cells,that i want is to copy only the values not the formulas.is possible to modify the code?Private Sub Worksheet_Change(ByVal Target As Range) Dim A As Range, intr As Range, r As Range Set A = Range("G:G") Set intr = Intersect(A, Target) If intr Is Nothing Then Exit Sub Application.EnableEvents = False For Each r In intr If r.Value <> "" Then r.Copy r.Offset(0, -4) End If Next r Application.EnableEvents = TrueEnd Submessage edited by zomis

Answer:vba code to copy only the values from cells not formulas

First, a posting tip: Please click on the blue line at the end of this post and read the instructions on how to post VBA code in this forum so that it is easier for us to read. As far as your question, replace this:r.Copy r.Offset(0, -4)with this:
r.Copy
r.Offset(0, -4).PasteSpecial xlValuesClick Here Before Posting Data or VBA Code ---> How To Post Data or Code.

5 more replies
Relevance 97.17%

Hi,

I'd like some help with a formula please. Have been working on this for hour and can't figure it out...

I need to compare the values of two cells, then multiply the lower value by 2.
For example:

............Column A.....Column B......Column C (this is what I'd like to calculate)
Row 6........50.............40...............80
Row 7........30.............30...............60
Row 8........25.............29...............50
etc...

This is what I'd like to say in the formula:
If A6 is lower than B6, then multiply A6 by 2. If B6 is lower than A6, then multiply B6 by 2. If A6 and B6 are the same, then multiply either by 2.

Any help is much appreciated.
Thank you in advance.
F. Jones
 

Answer:Solved: Compare values in two cells and multiply lower value by 2

In c1 try =min(a1:b1)*2
 

2 more replies
Relevance 97.17%

Using a macro, in cell range D2:D151, find values between 0 and 8 and if true display a message in cell F151 "No Jackpot this week". Can you help ?

Answer:how to find values in range of cells and display message

It's not clear to me what you are asking for.You say you want to search a range and find values (plural) between 0 and 8. Do you mean you are looking for at least 2 values any where in that range that meet that criteria or do all values in that range have to meet that criteria, or do mean something totally different?I'm also not sure why you want a macro, but that answer might depend on what you are trying to accomplish.Just as a shot in the dark, this formula will return your message as long as at least 2 cells in that range are greater than 0 but less than 8:=IF(COUNTIFS(D2:D151,">0",D2:D151,"<8")>1,"No Jackpot This Week","")Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

4 more replies
Relevance 97.17%

=SUM(LARGE(C20:F20:I20:L20,{1,2,3}))This work the worksheet as required, however the selected cells may number up to 50I have tried the following but know that I am missing something, but I can't see what=SUM(LARGE((C20,F20,I20,L20),ROW(INDIRECT("1:C2"))))C2 is a volatile number, subject to change given a specific value from elsewhere in the worksheet, so I could be looking for the total of the largest 2 values, or the total of the largest 12 values.message edited by tonygibb

Answer:Total three largest values from a selection of cells Part 2

=IF($C$2=1,SUM(LARGE((C19,F19,I19,L19),{1})),IF($C$2=2,SUM(LARGE((C19,F19,I19,L19),{1,2})),IF($C$2=3,SUM(LARGE((C19,F19,I19,L19),{1,2,3})),IF($C$2=4,SUM(LARGE((C19,F19,I19,L19),{1,2,3})),0))))Thank you so much for your reply, I understand your explanation, but the method of CTRL,Shift,Enter would tend for the end User to get confused.My intention is that they are to only to create the value for C2 and that the calculations take place without any other key motion.I have created the above nested IF function which will do what I require.I just have to amend it slightly to accommodate the number of values to be Sum totalledAgain thanks for your help

7 more replies
Relevance 96.35%

I want to insert into my database; 1 of the fields is a 'created' field. eg if its August then is I need to create a variable in vb with August in it. ANd Date onComm is Today's date (now).But the rest of the fields are from an linked xcel spread sheet. How do I code this? At present I have in SQL
INSERT INTO CommCustomer ( Accno, Contractno, DateonComm, Amount, CommDate )
SELECT [ Acc] AS Accno, Milcar.[Contract No ] AS Contractno, Now() AS DATEonComm, [ Rec] AS Amount, MonthName([DateonComm],True) AS CommDate
FROM Milcar
WHERE ((([ Rec])<>0));

But I get invalid procdure call; I actually want it in VB.

sql = "Insert INTO CommCustomer (Accno,CommMonth,Contractno,DateonComm,Amount)" & _
"SELECT "[ Acc], [Contract No ], [ Rec], " & _
" FROM Milcar " & _
" WHERE Rec <> 0 ;"

So my question is how do I insert 2 fields that are not from the select clause - I actually need to use values from them?
 

Answer:Insert with Values and values from a Select clause

6 more replies
Relevance 96.35%

'm making a spreadsheet that i want to be able to sort by date. The problem is, I have different levels of specificity for the dates - for some, we just have a year ("2000") or month and year ("December 2014") or a range ("2013 - 2014"). i want those to remain displayed as quoted in parenthesis, but I know I need to go down to mm/dd/yy to keep the sort right. So I'm okay with "2000" sorting as 1/1/2000 - but I can't figure out a way to have a different underlying value from what it actually sorts on, other than the very tedious different custom formatting for different types of dates. Ideally: I'd want to have these columns - "Date (Sort)" and "Date (Display)"...I want to be able to hide the sort column, but still have "Date (Display)" sort based on the values in the sort column. if there's a way to conditionally format for how it's displayed based on whether i enter month/day or just year, but still have it treated as a date in mm/dd/yyyy format for sorting - that would be even better.
Is there a way to do this?

More replies
Relevance 95.94%

Good day all.

I am trying to add a value from a specific range of values to a target cell by simply clicking one of the specific range of cells, i.e.

C2- Bill
C3- Bob
C4- Jim
C5- Mike
C6- Steve

I want to Click one of these cells and have the value in that cell show up in Cell A1

So clicking on C2 would add the value Bill to A1, then if i click C6 it would change the value in A1 to Steve etc.
 

Answer:Click to add Values in a different Cell

Welcome to the forums zelle

I don't know much about Excel programming, but I'm pretty sure you'll have to use VBA programming to do what you are trying to achive. I'm sure there will be some source code for changing a value based on the active cell - so perhaps some googling will yield a result.

Good luck
 

5 more replies
Relevance 95.94%
Question: Update cell values

I have lot of data in col. A to I running into many rows in sheet2. Also it contains a list in Col.N as shown below:applemangomanmonkey etc.Now i want if sheet1 col.A contains mango then its row in Col. B, C, G, J and L's data should be updated whenever i made changes in sheet2 row containing mango where:sheet2 Col.B = Sheet1 Col.Bsheet2 Col.I = Sheet1 Col.Csheet2 Col.G = Sheet1 Col.Gsheet2 Col.F = Sheet1 Col.Lsheet2 Col.E = Sheet1 Col.JFor your information:1. In sheet1 the Figures of sheet2 Col.I, G, F and E is divided by number 100000 using macro (eg. if sheet2 F=1245000 then sheet1 Col.L will be 12.45)2. I have "Private Sub Worksheet_Change(ByVal Target As Range)" macro in sheet1Can you provide a macro to accomplish what i want?

Answer:Update cell values

Just checking...Do you have your examples backwards?You said you want Sheet1 updated if Sheet 2 is changed, but then you have:sheet2 Col.B = Sheet1 Col.BWhich typically means that Sheet2 Col B will be set equal to Sheet1 Col B.Do you actually mean Sheet1 Col.B = Sheet2 Col.B after changes are made to Sheet2?

11 more replies
Relevance 95.94%

Can some body help?How can i copy a set of values to different cells. E.g. I have these numbers that keep occurring periodically in a column. And i want to copy each set of values (4 of them each) to a new column. E.g. So i have in one column with these valuesABCDEFGHthey are about 100 of such set of values. And i want to copy them to look like this.A E IB F JC G KD H L Who can help? Is there any function that can do this or i need to write a macro?I tried using a macro that i recorded, but I got an error saying too macro too large.Any hints?

Answer:Copy set of values to different cell

Is there really an empty row between the 4th and 5th value?If so, is there an empty row between every set of 4 values?Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

2 more replies
Relevance 95.94%

I'm trying to do a simple 2 column merge in an Excel file. The idea is to put both first and last names (currently in 2 separate columns) into one column. There is more data in the worksheet, address, telelphone etc - in other columns.

When the name columns are selected, then FORMAT CELLS, then ALIGNMENT, MERGE CELLS, it comes back with -

"The selection contains multiple data values. Merging into one cell will keep the upper-left most data only"

There's text only in all the cells in both columns.

When I click on OK - like yea, do it anyway - both first and last name columns are blank - and there is no merged column.

I have also tried to use the CONCATENATE function and it works great for 1 cell at a time, but does not do entire columns...

Is there another step I've missed in this ?

Thanks...
 

More replies
Relevance 95.12%

I am hoping that someone can help me with what may be a complex question.

I have a spreadsheet, similar to the demonstration file I have attached. In it, there are a range of columns. One set of columns (A - D), shows a list of numbers that are references to items in a store, and it also shows their location in the store, so it acts as a floor-plan. The next column (E) lists those items again to show the dollar amount that those items have produced in total sales (F).

So, what I'd like to do is to add a conditional formatting to show me the "cold" and "hot" spots on the floor-plan so I can see where sales are most concentrated. I would assume that this would be done with a colour scale (using the range of values in column F to work itself out)?

Either way, the figures in column F would change weekly, so the relevant cells in columns A - D would need to automatically change when they're matching cells in column E - F change.

I hope this makes sense, as it's difficult to explain, but any help that someone could provide would be greatly appreciated.
 

Answer:Solved: Creating Cell "Cold" And "Hot" Spots Based On Values In Microsoft Excel 2007

16 more replies
Relevance 95.12%

(Don 't be intimidated by the length of this post; the essence is simple; I'm just a bit talky; I think it gives a clearer picture )

Hi guys. This is my first time here. I've been searching for almost an entire year now for a solution to this, and found nothing. I found your forum. Thank you so much for all the help you've given to people. I hope one of you can find a solution for this one.

I work for a huge multinational company with a massive number of employees. One of my tasks is to maintain a database containing the training certificates the employees are awarded. Regularly (a few times a daily), I get requests from different departments asking if the so and so employee has been awarded something/anything. They always send me excel sheets containing employee numbers and I have to compare their sheet to the master sheet I maintain.

Now here's the tricky part: I have two columns only, one for the certificate number, and the other for the employee numbers (dummy file attached). I put all the employee numbers in one cell, separated by space. Is there a way I can search for a value inside a cell, instead of just simply matching cells? This can easily be done using the search function, but often I get huge numbers of employee numbers, and using Ctrl+F in such cases consumes a ridiculous amount of time.

My question is easier to understand if you open the file.
Thanks in advance.

Alex
P.S. A secondary request is to suggest to me a better way... Read more

Answer:Solved: Tricky one: possible to look for a value in a **cell** with many values?

16 more replies
Relevance 95.12%

I have an excel cell with the following values [I=406;A=5546.55;D=20110520|I=541;A=8486;D=20110520|I=477;A=11.05;D=20110524] and want to put the A and D values in their on cell. ie I want say value of A = 5546.55 should appear in a Cell as 5546.55

Answer:I am having a problem with extracting values from one cell

Here's a 2 step process:With your long string in A1, use the Data...Text To Columns feature to split the string into separate cells. If you use a semicolon ; and the bar | as your delimiters, you should get something like this:
A B C etc.
1 [I=406 A=5546.55 D=20110520 etc.
Once you have that, you can use this formula to extract the numbers after the equal sign:=SUBSTITUTE(MID(A1,FIND("=",A1)+1,LEN(A1)),"]","")Obviously, you would need to drag this formula across to update it for each column.The MID function extracts everything after the equal sign and the SUBSTITUTE function eliminates the bracket at the end of the string if it exists.Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

4 more replies
Relevance 95.12%

I can't seem to bust through this. I have a range of data, I would like to search through one column of data, and move the contents of the cells in one column to another column conditionally, if a third column's contents meets certain criteria. e.g.Description Happy Times Total Alert
AB+ HOGAN 400 4 1600
PG+ SAM 50 2 100
Lucite Counter 1500 1 1500
Total

PG+ SALLY 50 1 50
PG Gunther 100 1 100
In the sample data above, if the cells in the 'Description' Column - (Column A say) do not start with AB or PG, or the word 'Total' then move the Contents (a formula) of column 'Total' ( Column D ) to Column 'Alert' ( Column E )As you can see in the sample data, there are multiple ranges, so the significant data is Non-Contiguous. The result would be:Description Happy Times Total Alert
AB+ HOGAN 400 4 1600
PG+ SAM 50 2 100
Lucite Counter 1500 1 1500
Total

PG+ SALLY 50 1 50
PG Gunther 100 1 100
I *think* I should be using the OR function to determine AB, PG or Total, nested in an IF statement, as in my struggles at a formula below, but I dont know how to move the contents, which is a formula, or how to loop through the range... and then do it in VBA. =IF(OR(LEFT(A2,2)="AB",(LEFT(A2,2)="PG"),(LEFT(A2,5)="TOTAL")),"Leave_prod_total_in_DCOL","Move_value_in_Dcol_to_Ecol")(the text strings "Leave_prod... " and "Move_valu... " are just placeholders)For the Looping - I have seen examples from Humar and DerbyDad03 that show me how to determine the end of... Read more

Answer:Conditionally Move cell values with VBA

Let's start with an explanation of this:lastARow = Range("A" & Rows.Count).End(xlUp).RowlastARow is just a variable name. It could be X or Fred or intRowLength or any other non-reserved word.Range("A" & Rows.Count) is equivalent to Range("A65536") in Excel versions earlier than 2007 and Range("A1048576") in 2007.Using Rows.Count makes the syntax compatible with all versions of Excel since it lets VBA determine how many rows are in the spreadsheet..End(xlUp).Row tells VBA to go to the end of the range and look up until it finds data in a cell in that column. When it finds data in a cell, it returns that Row number.That said, I'm slightly confused by your data table. Is there a reason that you don't have 200 in the Total column for this value:PG+ SAM 50I'll assume it's a oversight.OK, now back to what you are trying to do.I'm not sure why you want to mix VBA with formulas. I think you can do it without any VBA. I can reproduce your table above (with the addition of the 200 mentioned earlier) with these 2 formulas:In the Total column:=IF(OR(LEFT(A2,2)="AB",LEFT(A2,2)="PG",A2="Total"),B2*C2,"")In the Alert column:=IF(AND(LEFT(A2,2)<>"AB",LEFT(A2,2)<>"PG",A2<>"Total"),B2*C2,"")Does that get you what you want?

5 more replies
Relevance 95.12%

Hi,I would like to write a macro in Excel 2003 that will copy the cell values of cells B2 to B7 and Cell N30 from 'Sheet1' to the next new row in 'Sheet2' (in columns A to G)I am a complete newbie to VBA and so don't even know where to start. Can anyone help please?

Answer:Copy cell values to new row on another sheet

I don't have access to Excel until Monday, so I'm writing this off the top of my head without any means to test it. If it errors out, let me know what it says and I'll try to fix it - or wait until Monday and I'll make sure it works.
Option Explicit
Sub CopyRange()
Dim nextRow as Integer
'Find next empty row on Sheet2
nextRow = Sheets(2).Range("A" & Rows.Count).End(xlUp).Row + 1
'Copy Sheet1!B2:B7, Transpose to Sheet2
Sheets(1).Range("B2:B7").Copy
Sheets("Sheet2").Range("A" & nextRow).PasteSpecial Transpose:=True
'Copy Sheet1!N20 to Column G
Sheets(1).Range("N20").Copy Destination:=Sheets(2).Range("G" & nextRow)
End Sub

7 more replies
Relevance 95.12%

I am trying to figure out a formula for a specific cell that will:

Add all of the "1" values in a given range and multiply by 10
Add all of the "2" values in the same range and multiply by 9
... etc.... to add all the "10" values and multiply by 1 (or basically not multiply by anything)

Then add these new values together to come up with a final number.

Does anyone have some way to do this?

BTW, all of the "1", "2", etc... values are actually vlookup results. I don't know how that will mess up the formula.
 

Answer:Solved: Adding cell values

11 more replies
Relevance 95.12%

I have an expense report in Excel, from the Extended totals column (G) for each expense I want to insert a column (H) that has an assignment Letter (A through E). The Next 5 Columns (I - M) are marked A through E as they relate to the different cost centers. How do I create a formula that lets me quickly code each line in Column G (My totals per line) by just inserting the letter A or B or C or D or E into the corresponding line in Column H and that total automatically copies over to the correct Column

Answer:Copy Values to a specific cell

In I1:M1 enter your Cost Center letters A - EAssuming your first Extended Total is in G2, enter this formula in I2 (adjust as required)=IF($H2=I$1,$G2,"")Drag this over to M2 and then drag I2:M2 down as far as you need.The key is to ensure you use the dollar signs exactly as shown.Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

3 more replies
Relevance 95.12%

I have combobox (active control) with list range, if I select a value from that list, I want to go to a cell in a column with that exact value. (Match the value selected from combobox)This code works, but only with the value "costs". I want to replace the static value "costs" which I put in, with a selection from the combobox.Private Sub combobox1_Change()Cells.Find(what:="Cost", After:=ActiveCell, LookIn _ :=xlValues, Lookat:=xlWhole, Searchorder:=xlByRows, Searchdirection:= _ xlNext, MatchCase:=False, SearchFormat:=False).Activate End SubThanks

Answer:I want to use the values selected in combobox to go to cell

The reason "Cost" is only being picked up is because you have What:="Cost"What i would do is replace the "Cost" with a variable which picks up the value from your comboboxLike this Dim FindVal
FindVal = Combobox1.Value
Cells.Find(what:=FindVal, After:=ActiveCell, LookIn _
:=xlValues, Lookat:=xlWhole, Searchorder:=xlByRows, Searchdirection:= _
xlNext, MatchCase:=False, SearchFormat:=False).Activate

Now when you change the combox value it will search for that value in your worksheet.

3 more replies
Relevance 95.12%

I am using Excel 2000 and wish to show negative currency values in a cell as, say, (40.99) if the value is negative, but as 40.99 if positive - what cell format will do this? The cell still needs to retain its negative valus as it may be included in a summation.

Thanks
 

Answer:cell formats for currency values

Look at the Excel command Format>Cell, Category: Number.
The selection you want should already be there.
 

2 more replies
Relevance 95.12%

Hi!

I have the attached workbook where I want to count the number if instances of IEAC(CPI) from one row when the value of corresponding cell on another row is between 0 and 10%.

Range to count "IEAC(CPI)" from would be D2 to CA2 (default in my original workbook), based on the percentage in the range D1 to CA1. In cell C5 I would like the number of instances of IEAC(CPI) when the values of D1 to CA1 is between 0 and 0,1, in cell D5 I would like the same only for when D1 to CA1 is between 0,1 and 0,2.

Please, can somebody advise on how to write the function, or better yet, the macro?

Best regards
TorChr
 

Answer:Solved: Count according to 2 different cell values

=SUMPRODUCT(--($D$1:$CA$1<=0.1),--($D$2:$CA$2="IEAC(CPI)"))

will do the less than or equal to 10% and IEAC(CPI)

can the percent be below 0%
=SUMPRODUCT(--(d2:ca2="IEAC(CPI)"),--(d1:ca1<0.2),--(d1:ca1>0.1))

if you need to be greater than = to 10% and 20%
=SUMPRODUCT(--(d2:ca2="IEAC(CPI)"),--(d1:ca1<=0.2),--(d1:ca1>=0.1))
 

2 more replies
Relevance 95.12%

G'day,

Is it possible to have a cell show the total calculation of all the values in an entire column?

I have a column that will display the quantity of particular items, but because I will be constantly adding to this column and it will be constantly growing, I need a way for the cell showing the total to calculate the entire column, not just a limited range. Is this possible?

Any help would be greatly appreciated!
 

Answer:Solved: Calculating Values In An Unlimited Range Of Cells In A Column In Microsoft Ex

6 more replies