My daily download has intermittent blank cells per row based on the column
heading. I need shift all the data to the left leaving the blank cells per
row on the right. I am interested in a macro or a formula or both. I have
attached a sample file with the required details.
Thank you for your
I have a download into Excel with start and stop cells which contain the
date/time. I need help making my formula work please to get the NET
minutes.
Please see my attached problem example.
Thank you for being here (again).
Very Grateful,
John
--
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in
Thank you very much for your helpful swift reply...
John
On Tue, Mar 5, 2013 at 11:55 AM, rajan verma rajanverma1...@gmail.comwrote:
Just remove starting space from the date
On Tue, Mar 5, 2013 at 9:54 AM, John A. Smith johnasmit...@gmail.comwrote:
I have a download into Excel
Excellent, that saves a step. Thank you very much.
John
On Tue, Mar 5, 2013 at 12:05 PM, xlstime xlst...@gmail.com wrote:
use your formula with value and trim formula
.
Enjoy
Team XLS
On Tue, Mar 5, 2013 at 10:24 PM, John A. Smith johnasmit...@gmail.comwrote
Thank you Viper ever so much!!!
John
On Wed, Feb 6, 2013 at 1:20 AM, The Viper viper@gmail.com wrote:
pfa
you can modify the range on formula as desired.
On Wed, Feb 6, 2013 at 9:34 AM, John A. Smith johnasmit...@gmail.comwrote:
Paul,
#1) By using conditional formatting, highlight
I have a spreadsheet with 180 columns and 8,000 rows. I would like to
highlight a row if a specific value is anywhere in the spreadsheet. (for
example in cell K23 = 234 and H112 = 234, then both row 23 and 112 are
highlighted).
Please help with the formula to accomplish this. Thank you.
John
Paul,
#1) By using conditional formatting, highlight any row the EXACT target
value is in regardless of the column it's in
#2) Has to be an EXACT match
#3) Helper columns are okay
#4) Rows quantity varies but can be between several hundred to
approximately 8-9,000
#5) Can be either formula or VBA
at 9:57 PM, John A. Smith johnasmit...@gmail.comwrote:
Ashish,
Thank you for the quick reply. I get an error when I copy and paste your
macro in my spreadsheet. I attached a screen print of the error. A user
defined parameter is missing??? When I first downloaded your sample it
opened my
Kris,
You are amazing and gracious for your help and very quick repsonse. Thank
you millions!
John
On Tue, May 8, 2012 at 10:09 AM, Krishna Kumar krishnak...@gmail.comwrote:
Hi
Try this
Sub kTest()
Dim ka, k(), i As Long, c As Long, n As Long, Hdr, x
Dim wks As Worksheet,
Absolutely fantastic! Thank you for your generous help.
John
On Tue, May 8, 2012 at 11:07 AM, Krishna Kumar krishnak...@gmail.comwrote:
Replace
ReDim k(1 To UBound(ka, 1) * (UBound(Hdr) + 1) + UBound(Hdr) + 1, 1 To 1)
with
ReDim k(1 To 1, 1 To 1)
Kris
ExcelFox
No dupes and double click to sort by column
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguille...@gmail.com
*From:* John A. Smith johnasmit...@gmail.com
*Sent:* Monday, March 19, 2012 1:03 PM
*To:* excel-macros@googlegroups.com
*Subject:* Re: $$Excel-Macros$$ Consolidate Two Weekly
))+IF(ISNA((MATCH($J4,'Week
Two'!$A:$A,0))),0,VLOOKUP($J4,'Week Two'!$A$2:$C$118,2,0))
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguille...@gmail.com
*From:* John A. Smith johnasmit...@gmail.com
*Sent:* Monday, March 19, 2012 10:00 AM
*To:* excel-macros@googlegroups.com
*Subject
the
summary sheet. It uses a macro to make a unique list and then get the data
for the unique list for each sheet.
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguille...@gmail.com
*From:* John A. Smith johnasmit...@gmail.com
*Sent:* Monday, March 19, 2012 11:09 AM
*To:* excel-macros
Don Guillett
SalesAid Software
dguille...@gmail.com
*From:* John A. Smith johnasmit...@gmail.com
*Sent:* Thursday, February 23, 2012 9:25 AM
*To:* excel-macros@googlegroups.com
*Subject:* Re: $$Excel-Macros$$ Chart Template Dynamic for Different Users
Xlstime,
I saw that but when I
.
Don Guillett
SalesAid Software
dguille...@gmail.com
*From:* John A. Smith johnasmit...@gmail.com
*Sent:* Friday, February 24, 2012 10:02 AM
*To:* excel-macros@googlegroups.com
*Subject:* Re: $$Excel-Macros$$ Chart Template Dynamic for Different Users
Don,
I spent hours going
Xlstime,
Where do I put that formula?
John
On Thu, Feb 23, 2012 at 10:10 AM, xlstime xlst...@gmail.com wrote:
Dear John,
Please find the attachment
use formula =OFFSET($A$2,0,0,COUNTA($A$2:$A$10),COUNTA($A$2:$F$2))
On Thu, Feb 23, 2012 at 8:13 PM, John A. Smith johnasmit
Xlstime,
I saw that but when I added another product it didn't show up on the
chart.
John
On Thu, Feb 23, 2012 at 10:20 AM, xlstime xlst...@gmail.com wrote:
in Name Manager,
press alt+i+n+d
On Thu, Feb 23, 2012 at 8:47 PM, John A. Smith johnasmit...@gmail.comwrote:
Xlstime,
Where
:* John A. Smith johnasmit...@gmail.com
*Sent:* Monday, February 06, 2012 8:21 AM
*To:* excel-macros@googlegroups.com
*Subject:* $$Excel-Macros$$ Need A Macro To Move Rows
Thank you for your ongoing Excel help. I need a macro to correct a
download issue. The download places one event on two
Sub
Regards,
Sam Mathai Chacko
On Mon, Feb 6, 2012 at 9:21 PM, dguillett1 dguille...@gmail.com wrote:
Glad to help
Don Guillett
SalesAid Software
dguille...@gmail.com
*From:* John A. Smith johnasmit...@gmail.com
*Sent:* Monday, February 06, 2012 9:11 AM
*To:* excel-macros
-macros@googlegroups.com] *On Behalf Of *John A. Smith
*Sent:* Jan/Thu/2012 08:49
*To:* excel-macros@googlegroups.com
*Subject:* $$Excel-Macros$$ Need Formula for Lookup Based on a Matrix of
Data
** **
Thank you for your valuable help.
I need a formula that will lookup
))
with ctrl+Shift+enter
See attached sheet.
--
Thanks regards,
Noorain Ansari
*http://excelmacroworld.blogspot.com/*http://excelmacroworld.blogspot.com/
*http://noorain-ansari.blogspot.com/*http://noorain-ansari.blogspot.com/
On Thu, Jan 26, 2012 at 8:48 PM, John A. Smith johnasmit
:48 PM, John A. Smith johnasmit...@gmail.comwrote:
Thank you for your valuable help.
I need a formula that will lookup a value based on a column heading, a
date column/row and a row qualifier (the word Total). Please see the
attached spreadsheet.
Thank you.
John
--
FORUM RULES (986
-ansari.blogspot.com/*http://noorain-ansari.blogspot.com/
On Thu, Jan 26, 2012 at 8:48 PM, John A. Smith johnasmit...@gmail.comwrote:
Thank you for your valuable help.
I need a formula that will lookup a value based on a column heading, a
date column/row and a row qualifier (the word Total). Please
Excel Teachers,
I need to look at a cell and if it contains specific characters (for
example the three characters “410”), return a specific value and if not
return a blank. The string can be quite long or short and the “410” may or
may not be in it and its location when it is in it could be at
Thank you Don, I appreciate the help.
John
On Tue, Nov 29, 2011 at 5:04 PM, dguillett1 dguille...@gmail.com wrote:
Don Guillett
SalesAid Software
dguille...@gmail.com
*From:* John A. Smith johnasmit...@gmail.com
*Sent:* Tuesday, November 29, 2011 3:08 PM
*To:* excel-macros
Perfect! Thank you Ashish. Thank you very much.
John
On Thu, Nov 17, 2011 at 9:45 AM, ashish koul koul.ash...@gmail.com wrote:
Sub test()
Dim rngap As Range
For Each rngap In Sheets(1).Range(m5:q17)
rngap = False
Next
End Sub
On Thu, Nov 17, 2011 at 8:11 PM, John A. Smith johnasmit
, 2011 at 8:22 PM, John A. Smith johnasmit...@gmail.comwrote:
Perfect! Thank you Ashish. Thank you very much.
John
On Thu, Nov 17, 2011 at 9:45 AM, ashish koul koul.ash...@gmail.comwrote:
Sub test()
Dim rngap As Range
For Each rngap In Sheets(1).Range(m5:q17)
rngap = False
Next
End
you can.” - John Wesley
*-
--
*From:* John A. Smith johnasmit...@gmail.com
*To:* excel-macros@googlegroups.com
*Sent:* Tue, November 8, 2011 2:16:05 PM
*Subject:* $$Excel-Macros$$ Macro To Add a Cell From A List And Create
= Cells(Rows.Count, 1).End(xlUp).Row
For Each c In Range(a2:a lr)
With Sheets(form)
Range(d56).Value = c
.Copy after:=ActiveSheet
End With
Next c
End Sub
Don Guillett
SalesAid Software
dguille...@gmail.com
*From:* John A. Smith johnasmit...@gmail.com
*Sent:* Tuesday, November 08
Haseeb,
Thank you for that. That solved an unrelated issue and was very helpful,
thank you.
John
On Wed, Nov 2, 2011 at 8:22 PM, Haseeb Avarakkan haseeb.avarak...@gmail.com
wrote:
Hello John,
If the Arrival Date Time is 09/08/2011 2310 Length is 343 (Row 3
data) Finish date time is
Sam,
Thank you for that, it saves a column.
John
On Wed, Nov 2, 2011 at 9:25 PM, Sam Mathai Chacko samde...@gmail.comwrote:
If you only want the finish time without splitting it in to Date and Time,
you could also use
=LEFT(TRIM(A23),10)+TEXT(RIGHT(TRIM(A23),4),00\:00)+(B23/(60*24)) in
Excel Teachers, I need to format 09/14/2011 0938 into 9/14/11 (or a real
date format). Please see attached spreadsheet.
Thank you for your help.
John
--
--
Some important links for excel users:
1. Follow us on
at 9:22 PM, John A. Smith johnasmit...@gmail.comwrote:
Excel Teachers, I need to format 09/14/2011 0938 into 9/14/11 (or a real
date format). Please see attached spreadsheet.
Thank you for your help.
John
Esteemed Group,
I would like to know if there is a formula that would allow you to type ian
in a cell and have it look up and find Giant Wing in a list of unique
words. Please see the attached example.
Thank you very much for your help.
John
--
Thank you Ashish for your quick and very enlightening response.
John
On Fri, Oct 21, 2011 at 7:28 AM, ashish koul koul.ash...@gmail.com wrote:
VLOOKUP(*H4*,E:E,1,0)
On Fri, Oct 21, 2011 at 4:49 PM, John A. Smith johnasmit...@gmail.comwrote:
Esteemed Group,
I would like to know
options.
On Fri, Oct 21, 2011 at 4:49 PM, John A. Smith johnasmit...@gmail.comwrote:
Esteemed Group,
I would like to know if there is a formula that would allow you to type
ian in a cell and have it look up and find Giant Wing in a list of
unique words. Please see the attached example
I need the formula to lookup a value in a table and return the table column
header value. Please see attached spread sheet.
Thank you for your kind help.
John
--
--
Some important links for excel users:
1. Follow
:44 pm, John A. Smith johnasmit...@gmail.com wrote:
I need the formula to lookup a value in a table and return the table
column
header value. Please see attached spread sheet.
Thank you for your kind help.
John
LOOKUP Value in a Table and Return Table Header Value.xlsx
Oct 2011 20:46, John A. Smith johnasmit...@gmail.com wrote:
Dilip,
Thank you for the quick response. It works great. One other question;
How could I change the formula to pick up the next period if data is added
to cell I3 (through I13)?
I marked up the spreadsheet for my new question
Excellent! Thank you Haseeb, that works like a charm. Thank you for you
help.
John
On Fri, Oct 7, 2011 at 2:02 PM, Haseeb Avarakkan haseeb.avarak...@gmail.com
wrote:
Hi John,
Try these,
K3, copy down.
=LOOKUP(LOOKUP(9E+300,C3:J3),N3:X3,N$2:X$2)
L3, copy down.
..and
right clickselect group...and select group by year..
you can also group my month Quarter too..
Regards
Chethan Kumar BN
On Tue, Oct 4, 2011 at 1:32 AM, John A. Smith johnasmit...@gmail.comwrote:
Attached is a spreadsheet from this forum. I would like to know how the
Year field
Excel Experts,
I need to calculate the time differences between 11:30:00 AM and 11:37:00 AM
and show the 7 minutes in the decimal hour format.
Please see attached sample spreadsheet for the solution I need.
Thank you for your continuing Excel help.
John
--
Thank you Noorain. I appreciate your help very much.
John
On Wed, Aug 31, 2011 at 1:26 PM, NOORAIN ANSARI noorain.ans...@gmail.comwrote:
Dear john,
Please see attached sheet..
On Tue, Aug 30, 2011 at 9:39 PM, John A. Smith johnasmit...@gmail.comwrote:
Your help please. I have a log
Your help please. I have a log of product sold and need to calculate the
different product sold to date, like a running accumulation field specific
to product listed in column B.
Please see my attached spreadsheet. Thanking you in advance for all your
great help.
John
--
Thank you very much for your quick response, your patience and your help.
John
On Tue, Aug 30, 2011 at 12:37 PM, dguillett1 dguille...@gmail.com wrote:
in d2 copied down
=SUMIF($B$2:B2,B2,$C$2:C2)
*From:* John A. Smith johnasmit...@gmail.com
*Sent:* Tuesday, August 30, 2011 11:09 AM
This is exactly what I was looking for. Again thank you for teaching us!
John
On Thu, Aug 18, 2011 at 11:41 AM, ashish koul koul.ash...@gmail.com wrote:
try this
On Thu, Aug 18, 2011 at 8:51 PM, John A. Smith johnasmit...@gmail.comwrote:
I have a dashboard that looks at a rolling average
Excel guru's, can I make a receipt file that adds 1 to a specific cell
everytime the file is opened?
Please see attached.
Thank you.
John
--
--
Some important links for excel users:
1. Follow us on TWITTER for
So either this didn't post, or there is no way on earth for this to happen.
John
On Fri, Jul 15, 2011 at 2:39 PM, John A. Smith johnasmit...@gmail.comwrote:
Is there a way to have my Quick Access Toolbar portable (like on a jump
drive) so any computer with Excel 2007 can have my customized
Is there a way to have my Quick Access Toolbar portable (like on a jump
drive) so any computer with Excel 2007 can have my customized Quick Access
Toolbar when I use it?
Thank you.
John
--
--
Some important links
Dear Excel Teachers,
I need a formula to give me the decimal hours between two dates. I have
attached a sample.
Thanking you in advance for your on-going help and wisdom.
John
--
--
Some important links for excel
, John A. Smith johnasmit...@gmail.comwrote:
Please, attached is an example of data which I need to transpose and a
pivot table doesn't do it...
Thank you for your help.
John
--
--
Some important links
Please, attached is an example of data which I need to transpose and a pivot
table doesn't do it...
Thank you for your help.
John
--
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks
It helped very much Ashish, thank you again for your kind teachings.
John
On Fri, Jun 3, 2011 at 1:43 PM, ashish koul koul.ash...@gmail.com wrote:
see if it helps
On Wed, Jun 1, 2011 at 7:07 PM, John A. Smith johnasmit...@gmail.comwrote:
Need help please extracting specific data from
Need help please extracting specific data from an Excel spreadsheet into an
Excel report.
The arrangement of the data isn't always in the same order as shown in the
attached sample.
Thank you again for your kind help.
John
--
Sub NOWENTRY()
'
Please help with my macro to enter current time and date into any cell I
choose. It always goes back to the cell in the macro and I want it to work
for the current cell the cursor is sitting on when I hit Ctrl+n.
My Macro:
' NOWENTRY Macro
' Enters current time and date
'
'
Excellent! Thank you very Much!
John
On Tue, May 24, 2011 at 11:44 AM, ashish koul koul.ash...@gmail.com wrote:
try this
Sub test()
ActiveCell.Value = Now()
End Sub
or
Sub test()
Range(B5029).Value = Now()
End Sub
On Tue, May 24, 2011 at 8:39 PM, John A. Smith
I need a macro that will move the curser in a specific pattern of; Right one
cell the first time, and down one and left one the second time and then
repeat.
It would save a lot of time entering the data in two side by side columns so
I need it to not be column specific (so I could use it in any
Again, thank you Ashish very much. You have taught me a lot.
John
On Sat, Apr 9, 2011 at 6:27 AM, ashish koul koul.ash...@gmail.com wrote:
in excel 2007 format
On Fri, Apr 8, 2011 at 2:49 AM, John A. Smith johnasmit...@gmail.comwrote:
Ashish,
One more thing please; that macro
Ashish,
Perfect! Thank you for your patience and your teaching.
John
On Thu, Apr 7, 2011 at 11:22 AM, ashish koul koul.ash...@gmail.com wrote:
try this
run macro to merge the data and check sheet 2 and 3 for formula
On Tue, Apr 5, 2011 at 11:10 PM, John A. Smith
johnasmit
, John A. Smith
johnasmit...@gmail.comwrote:
Ashish,
I have attached the My Questions tab as a file by itself. Thank you for
your kind help.
John
On Tue, Apr 5, 2011 at 12:55 PM, johnasmit...@gmail.com wrote:
Ashish,
There are 22 employees. I don't need any blanks if no data
Formula Help Needed
i think my question tab is missing
do you want to show the blank records if the data of the employ is missing
in day
also how many total employees you have
On Tue, Apr 5, 2011 at 2:03 AM, John A. Smith johnasmit...@gmail.comwrote:
I receive an Excel file download
Thank you GoldenLance.
John
On Tue, Mar 29, 2011 at 3:00 PM, GoldenLance samde...@gmail.com wrote:
Try this
=IF(ISERROR(AVERAGE('Dept A'!B10,'Dept B'!B10,'Dept C'!
B10)),,AVERAGE('Dept A'!B10,'Dept B'!B10,'Dept C'!B10))
On Mar 29, 7:52 pm, John A. Smith johnasmit...@gmail.com wrote:
I
I have like tabs from different departments and need to summarize an average
score by day. But if there were no scores in a particular day, I get
#DIV/0!. Please help with the formula that eliminates it. I tries an
IFERROR in front of the AVERAGE but got a blank cell.
Thank you.
John
--
, and check if it helps
=IF(COUNT('Dept A:Dept C'!B3)=0,,AVERAGE('Dept A:Dept C'!B3))
learn 3D Reference more at:
http://office.microsoft.com/en-us/excel-help/create-a-3-d-reference-to-the-same-cell-range-on-multiple-worksheets-HP010102346.aspx
On Tue, Mar 29, 2011 at 9:52 PM, John A. Smith
, Viswanathan M vis...@gmail.com wrote:
Dear Mr. John,
=SUM(NOT(B2=),NOT(D2=),NOT(F2=))
The above formula will help you for finding out the Total Deliveries
*With warm regards*
*Viswanathan M*
DE(Tech)
RGM TTC Chennai-600027
*Vande* *Mataram*
On Sat, Mar 26, 2011 at 9:57 PM, John
I have an excel database where each line represents an entity and it's data
over about 70 columns. There are multiple occurrences of delivery
information in these columns that I need to summarize in a pivot table.
Some lines may have only one occurrence of this delivery while some others
may have
So I am guessing since I didn't get a single response from this posting that
it can't be done?
John
On Tue, Mar 1, 2011 at 2:41 PM, John A. Smith johnasmit...@gmail.comwrote:
Hello,
I need to create a chart that is based on the percent of calls closed. But
I also need to know if the 100
Hello,
I need to create a chart that is based on the percent of calls closed. But
I also need to know if the 100% is one out of one or ten out of ten. So I
would like the numbers it took to make the percent to be at the bottom in a
data table. I can get it one way or the other and need some
This is excellent. Thank you for your help.
John
On Thu, Feb 24, 2011 at 11:07 PM, Rohan Young rohan.j...@gmail.com wrote:
Hi,
see the attachment if it help
thanks regds,
ROHAN
On Thu, Feb 24, 2011 at 8:05 PM, John A. Smith johnasmit...@gmail.comwrote:
Your kind help please.
I
Your kind help please.
I have a database that I need to see duplicated rows but can't filter
because some columns can legitimately repeat. If the entire row is a
duplicate, I need to know.
Please see attached example. Thanking you in advance for your continuing
help in learning Excel.
John
if it helps
With OutMail
.to = ActiveSheet.Range(b I).Text
.CC = ActiveSheet.Range(c I).Text
On Wed, Jan 26, 2011 at 1:51 AM, John A. Smith johnasmit...@gmail.comwrote:
I use a file with a macro (which this wonderful group collectively
contributed to) which when I
I use a file with a macro (which this wonderful group collectively
contributed to) which when I highlight a range and hit ctrl + s it drops it
into a new excel file, opens outlook and addresses it to the addresses in
the macro.
I would like to let the user put an email address in a cell and when
Dave,
I opened it in Excel 2007 and it worked fine. One question please; in
=LOOKUP(2,FIND(A1,Sheet2!A:A),Sheet2!B:B) , What exactly does the 2 do?
Thank you for your excel help.
John
On Sat, Jan 15, 2011 at 2:16 AM, Dave Bonallack
davebonall...@hotmail.comwrote:
Hi John,
I opened your
Dave,
Why am I getting #NUM! with the formula? (See Attached)
Thank you.
John
On Fri, Jan 14, 2011 at 10:35 AM, Dave Bonallack
davebonall...@hotmail.comwrote:
Hi,
I would like to submit the following formula as one I like.
It performs a case-sensitive Vlookup, and is non-array.
Dave,
Why am I getting #NUM! with the formula? (See Attached)
Thank you.
John
On Fri, Jan 14, 2011 at 10:35 AM, Dave Bonallack
davebonall...@hotmail.comwrote:
Hi,
I would like to submit the following formula as one I like.
It performs a case-sensitive Vlookup, and is non-array.
Is there a way to change a filtered view by changing the value of a cell
rather than clicking the drop-down arrow and scrolling to the data you want
to filter it to?
Or more specific, see attached for a formula to look up all instances of a
specific variable.
Thank you.
John
--
76 matches
Mail list logo
