Close Window   
Free Ebook
Get Your Free Excel
ebook!
Our Top 15 Excel Tutorials
Instant Access!
E-mail:
Subscribe for Free Excel tips & more!
E-mail:
Advertisements



Subtracting Column B From A, How To Leave Third Column Blank If No Value In Column B?

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

Hello - I have what is proabably a basic question, but I could not find the answer. I have a spreadsheet with three columns (A,B,C). I want the third column C to be column A - B (A minus B) for each row, but only if there is a value in column B. If there is no value in column B, then I want that row in Column C to just stay blank. Is this possible? Thank you.

View Answers     

Similar Excel Tutorials

Get the Row or Column Number of a Cell in Excel
How to get the row or column number of the current cell or any other cell in Excel. This tutorial covers important ...
Delete Blank Rows in Excel
This is a macro which will delete blank rows in excel. This version will delete an entire row if there is a blank ...
Find the Next Blank Row with VBA Macros in Excel
Learn how to find the next empty cell in a range in Excel using VBA and Macros.  This method will skip any blanks ...
How to Add, Remove, and Rearrange Columns and Rows in Excel
Adding, removing and rearranging columns is as easy as resizing. You just select your columns or rows, right click ...

Helpful Excel Macros

Reverse Row or Column Order in a Worksheet
- This macro reverses the order of a selected row or column in excel. It will completely reverse the data.
Highlight the Column of the Selected Cell
- This macro will highlight the column of the active cell. This will fill the column of the active cell with a solid back
Highlight the Row and Column of the Selected Cell
- This macro will highlight the row and column of the active cell. This will fill the column and row of the active cell w
Delete Blank Rows in Excel
- This is a macro which will delete blank rows in excel. This version will delete an entire row if there is a blank cell
Vlookup Macro to Return All Matching Results and Stack them with Previous Results
- This is very similar to the other Vlookup type Macro in that it returns all of the results that match a particular se

Similar Topics









Hello everyone. I need help with the following:

I have two columns:
Column A is for entry of any date in cell C6
Column B contains a formula (i.e., =C6+10) to calculate a due date 10 days from the date in Column A

The problem is, anytime I erase the date in Column A, or leave it blank, a default date appears in Column B (i.e., 1/10/00).
Is there a way to leave Column A blank and at the same time have Column B blank as well?



Hello! New user, first time posting. Nice to meet you all! I'll get right to my question!

Would it be possible to pack this into one supercharged formula via COUNTIFS or VLOOKUP with nested IFS etc.? I am having a heck of a time. Any help would be appreciated. I use Excel 2010.

Criteria 1: If Column X:X is not blank, Column Z:Z is blank, and Column AB:AB is blank, then pull date from Column N:N
Criteria 2: If Column Z:Z is not blank, Column X:X is blank, and Column AB:AB is blank, then pull date from Column O:O
Criteria 3: If Column AB:AB is not blank, Column X:X is blank, and Column Z:Z is blank, and BOTH Columns N:N and O:O are not blank, then pull the LATTER of the dates in Columns N:N and O:O

My undying gratitude to any of you heavyweights that can help me solve this riddle. I don't seem to be grasping the essence of at least one factor of this formula that could make it solvable.

Internet hugs, and Happy Fourth of July for any fellow Americans here (almost)!
Aimee in Madison

Hi,

I have a column(A) which has figures and blank entries.

In column(B) im using the =whatever is in column(A).

In column(B) it is returning the answer as 0 if column(A) is blank. But i want it to return blank if column(A) has a blank not a 0.

Can you help please.

Thanks

Ru


Hello,

I want to compare two columns of data, and where ever there is a match, to subtract.

So in attached spreadsheet, I want to compare column A with column F, and if there is an exact match, then display the difference of coulmn i and d, all else leave blank.

See desired result in column K

How can I do this via sumproduct or vlookup, or any other method?

Thanks


I have several spreadsheets with 12 columns (A:L). I have a master sheet that consolidates the others but decides what to pull in based on whether or not there is a value in column A (therefore it ignores blank rows). Therefore, I need a way to ensure that something always gets placed in column A if there's another value in that row. I tried doing it with a basic If/Then statement but it doesn't really work for me.

Code:

For cell A2:  =IF(D2"","MA",IF(E2"","MA",IF(F2"", "MA","")))
For cell A3:  =IF(D3"","MA",IF(E3"","MA",IF(F3"", "MA","")))
So on and so forth for column A


I tried this with a macro but had even less luck.

Here's what I need to do: If there's a value in column D, E, or F, put MA in column A (UNLESS there's already a value in column A, then leave it as-is). I'd like to replicate this for all of column A (excluding A1) but don't want 65336 rows to show up when I try to print it. Can anyone help?




Hi, guys.
First time poster, long time viewer. Big fan!
im having a column of empty cells and some numbers. Like you see underIt goes on for hundreds of lines.

A B C
<blank>
<blank>
<blank>
4
<blank>
<blank>
<blank>
<blank>
<blank>
<blank>
15
<blank>
<blank>
<blank>
<blank>
8

I want to make a shorter version og column A in column B where all the blanks are removed. Like this:
A B C
<blank> 4
<blank> 15
<blank> 8
4
<blank>
<blank>
<blank>
<blank>
<blank>
<blank>
15
<blank>
<blank>
<blank>
<blank>
8

Does anyone know witch set of formulas i can use to get this done in excel 2010?????

I have two columns in my spreadsheet. Column A has no blank fields, Column B has some blank fields. I would like to show the text from Column A in the same row of Column B only when Column B is blank.

So, basically I'm after: If column B has text, do nothing. If column B is blank, then list text from Column A.

Right now I've created Column C with this formula =IF(ISBLANK(B1), A1. That's working to get the text from A1 when B1 is blank. What can I do/add to get it pull the text from B1 if there is text there? Or...is there a better approach altogether?


For example in column A I have value number 5 and in Column C I have 7, difference between Col A and Col C is 2 that answer I need in Col B.

But at the same time if I have a value 7 in Column A (greater value then column C )
And value 5 in Column C (lesser value then column A) in this case I want to consider Column C is 5+10=15 so difference between 7 and 15 is 8 which I want to get as a answer in Col B.

It means any time if the C column has a greater value it will minus from the A columns lowest value and show the answer or difference in Column B.
but if the C Columns value is less then the A Column then C Columns value will get added to +10 to any values already in C column (example 2+10 or 5+10)

I think in excel, if( ),> and < formula will work for this but I do not know how to write the formula.
If anybody can help me,

Thanks I Appreciates



I have a project I am working on for work. It involves 3 columns: Column A is the person's name, Column B is the computer name of the user submitted by the user, and Column C is the computer name of the user as provided from a report. Some users left Column B blank so we ran the report in Column C to try to fill in the blank cells. I need to merge columns B and C. I want to merge the columns so that column C is the "master" column and column B only fills in the blank cells of column C.

I have read through about a dozen posts about merging columns but I could not find one where one column is a "master" column that is not overwritten unless there are blank cells.

Any help would be greatly appreciated.




I'm sure this is very simple but I'm a bit rusty.

Column D contains blanks or information that begins with an alpha or numeric figure.
IF D is blank, I want to leave column F blank but if D contains any alpha or numeric figure then I want to put the contents of E in column F.

How do I write this question and in what column do I put the question in?

Hi!

I have a question regarding filtering of columns, hopefully someone is able to put me on the right track. I have set up Excel 2007 so that I can click on the drop-down menu in each column to filter them.

However, in my data set there is one blank row separating two set of rows. Now, when I apply the filter on a column, all the rows above the blank row filter correctly, but all the rows below stay un-filtered.

I guess Excel only looks at the consequtive rows, then stops when it hits a blank row. Is there any way of applying the filter beyond a blank row (i.e. the whole column)?

Any advice is higly appreciated.


I have two spreadsheets. The first spread sheet has three columns and the second spreadsheet has two columns. I want to take column "A" in the second spreadsheet and compare it to column "A" in the first spreadsheet and if it finds a match then I want it to print what is in column "C" on the first spreadsheet into column "B" on the second spreadsheet.

EX:

spreadsheet #1
Column A Column C

2 3
4 5
6 7
8 9

Spreadsheet #2
Column A Column B
5 blank
10 blank
4 5
8 9




i hope anyone help me to solve my question,
see example file,

if A column is BLANK or SPACE then B column should display "AUDIT DONE"
if A column Fill in any words then B column should display "BLANK"

if B column is BLANK or SPACE then C column should display 'NOT DONE"
if B column is AUDIT DONE then C column should display "BLANK"

NOTE " blank cell is not really blank, it just appear look like blank, that will be in formula with blank result,
AND i given just sample excel, which is really blank not filled in formula, but, in my original excel have formula in A,B columns..

i need to create pivot table, so that only i need this columns..

I am new to the forum but have found some helpful stuff here so far.

I have a project that I am working on and have over 68 thousand records that have all the data in one column that I need to get into multiple columns. The good thing is each record is broken up by a blank row but some records could have 1 line or 6 lines that I need to move to columns. If there is only 1 row then it goes to column 6, 2 rows then they go to column 5 and 6, 3 lines column 4, 5 and 6...etc.

Example:
data
data
data
<blank row>
data
<blank row>
data
data
<blank row>
data
data
data

Desired result:
Column 1 Column 2 Column 3
this didnt come out right. if i need to i can poste the spreadsheet

Thanks,
Russell


My excel workbook contains a single column of data, arranged as follows:

Column 1 Data 1
Column 2 blank
Column 3 Data 2
Column 4 blank
Column 5 Data 3
Column 6 blank
Column 7 Data 4
Column 8 blank
Column 9 blank

and then it repeats.

I need a macro to automatically rearrange each group of data into a single row of 4 columns as follows: Data 1 Data2 Data3 Data 4, then go the next row and display Data 5 Data 6 Data 7 Data 8, etc.




Hi,

I am fairly new to VBA and survived until today by just copying and pasting codes from the internet. However, I can not seem to find an answer to what I need to do now as I think that it is too simple. Here it is: I have columns that include 15-digit alphanumeric characters (IDs) that differ as to how many rows. All I want to do is to copy the non blank cells in each column and paste them to a new sheet and in just a single column. So if there are 8 rows in Sheet 1, column A, 15 rows in column B and 6 rows in column C that are not blank, I need Sheet 2, Column A to list 29 IDs.

Also, I am just planning to formulate in Sheet 3 that in every 60 IDs in Sheet 2, Column A, a cell would concatenate the 60 IDs in a single cell and separate them by a comma. I know that this might be easy to do in VBA as well so if you may kindly assist when you're feeling a little generous. It's that season anyways.

Thanks a lot in advance!

Marc

I have a spreadsheet with multiple columns of data. The columns contain answers to a multiple choice quiz taken by various individuals. The format of the columns is as follows:

Q1A
Q1B
Q1C
Q1D
Q1E

Q2A
Q2B
Q2C
Q2D
Q2E, etc.

In other words, there is a column for each possible multiple choice answer for all 10 questions. If a user selects answer "B" for question 1, the letter "B" appears in the column Q1B, and all other columns for Q1 are blank. So in this example for Q1, there would be 5 different columns which would appear as follows:

Q1A: this cell is blank
Q1B: contains character "B"
Q1C: this cell is blank
Q1D: this cell is blank
Q1E: this cell is blank

I would like to move this data from these various cells to one cell. In other words, I would create a new column for Q1, and I'd like to move all of the various answers to that new column. So in the example above, I would want one new column for Q1 with the character "B" in it. I would then repeat this exercise for all 10 of the answer fields.

Can this be done?


Forgive me if I've posted in the wrong place I'm new I looked around couldn't find my answer and felt this was the correct place to post my question.

Column A
5
5
5
5

5
5
5

5

20

This is basiclly what I'm looking for sure I could just =SUM(A1:A4) but then if I move things around I have to change the forumla. What I'm looking for is like a conditional SUM where I sum column A until I find a blank value. Is this possible?


Hi

I've posted my question before but I didn't get satisfactory answer, so I'll try again explaining it simpler. If you want the complex version go to

http://www.excelforum.com/excel-gene...-no-blank.html

I've got a table of data that looks like this:

A B C
01.08 aa
02.08 <blank>
03.08 <blank>
04.08 <blank>
05.08 <blank>
06.08 bb
07.08 <blank>
08.08 <blank>

I want to calculate a new column of values. When in column B appears a code ("aa", for example), I want to apply a formula, that is also applied to the blank cells below, until next code, when the formula changes. It is, I want to drag down a formula (not a value).

Example;
When the code "aa" appears in column B, the value in the column C is calculated with the formula =2*(A1 - $A$1)
The next cell of column C would be =2*(A2 - $A$1)
and so on

When we reach the row of cell with the code bb, the formula in the column C changes, for example
=5*(A6 - $A$6)
and the next cell below would be
= 5*(A7 - $A$6)


If required more details, see the attached file.


Hi, i hope you can help me with this one i have an excel spread see where some of my columns are blank, however i want to do some multification please see example below:
Column A1:A3 has some number, A4 is blank A5:A8 has numbers, A9:A12 is blank
B1:B12 has some numbers eg 10,9,36.......
Now in (column C) i want to multiply the data in (column A) with the data in (column B) however if there is a blank cell in (column A) then it need to find the next data above to multiply by. so if A4 is blank then it need to use A3*B4...
please bear in mind that there can be more than 1 blank cell in (column A) so if A4 and A5 is blank then it need to use A3*B4 and A3*B5...
Hope this makes sense

Regards
nick


I am using Excel 2003 and I need to sort and match three columns:

Column A:
Column B
Column C

About Column A:
Column A contains 40 rows of data (numerical codes in each cell). I would like to sort Column B and Column C according to the sequence of data in Column A. For instance, if there is a cell with 17685 in Column A, I want the 17685 in Column B to line up in the same row as it appears in Column A, and for the corresponding value in C (which is referenced to Column B but not the same type of numerical code as Column A or Column B) to also line up. When there is no match in Column B for a cell in Column A, I want to see a blank cell in Column B.

About Column B:
Column B contains 33 rows of data, all of which also exist in Column A but they are not currently aligned with those numbers in Column A. Further, Column B has fewer rows than Column A and I need Column B to display blank cells where there is no match with Column A.

About Column C:
Column C contains 33 rows of data, none of which contain the data/numbers in Column A and Column B, but which should be referenced to cells in Column B.

I have tried to use the sort function but have not been able to achieve what I want. I sense it must be very simple but I cannot get the three columns to line up (with blank cells) as I need them. I would be very grateful for any help. Thank you very much.


Hi!

I have a spreadsheet with values that change each day which currently requires a lot of manual manipulation. Part of the manipulation involves removing from a row any negative value in which there is no preceeding positive amount.

For example, column A is blank, column B is blank, column C is -$100, column D is $100, column E is -$100. I need to remove the -$100 from column C but leave the other values. The problem with using a macro is the value in any given cell will not be the same from day to day, so telling it to delete the value in C3 won't always be right.

I do it now by filtering the first column on any value less than 0, delete all those items, then change the filter to blank, move to the next column and repeat, the next column repeat, etc.

Is there a macro that can be made smart enough to selectively delete data based on this logic? I've also tried to do this same thing in Access but am unable to get it to work there, either.

Thanks for any help.


All--I am working with an excel spreadsheet with several hundred rows and 60 columns. The column of the first filled cell in each row varies. For example, row 1 may have blank cells until column 5, row 2 until column 15, row three until column 3, row four until column 7 etc.

I need to fill all blank cells on the left of the worksheet (from first to last row) with the number 1. I could simply fill all blanks except there are blank cells on the right side of the worksheet that need to stay blank. row 1 may end with column 14 (15 thru 60 are blank), while row 2 goes to 30, and row 3 goes to 45, etc.... The empty cells on the right side need to stay that way.

My thoughts so far are to write a macro to count the number of empty blanks in each row with a Match statement...then somehow fill in those blanks using the result of the statement in each row...working my way down the spreadsheet. Any help or ideas that you can provide would be appreciated. I have worked a great deal with functions in excel but have a lot less experience with macros.

kim


Hello all,

I am having the following situation.

Column A has different values and some rows are blank.

I want in Column B to check the equivalent cell in column A and work its way upwards until it finds a numerical value, then show that value.

So for example I have this

A
1
blank
blank
2
blank
3
blank
blank
blank
4
5
blank
6

I want in column something like IF(A3="",xxxxx,"") where xxxx is the formula that looks up column A starting from A3 upwards and when it finds the value (i.e in this case "1") to show that value.

I hope I'm making sense.

Regards and thanx for any answers.

K


Hey guys,

I have a spreadsheet with 2 columns, both displaying figures in dollars and cents. Column H is manually entered, and Column M is the product of a formula. What i would like to do, is if Column M is within 1 cent of column H, then round M up (or down) to column H - but if it's more than 1 cent difference, to leave the value derived from the formula.
All rows in the 2 columns have differing figures.
An example is this:
Code:

H         M
0.61     0.61
0.45     0.44
0.45     0.47


So that basically, the first and third rows (for column M) stay the same, but the 2nd row, the figure in Column M changes to that of column H?

Is this possible? And how would i do this?

Thanks guys