Eliminate unused columns
I have a table of columns of data per week, which I convert to a table of
columns of data per month. This leaves 3 or 4 blank columns between the
months. Is there a simple way to consolidate the month columns into a
contiguous region, eliminating the unused columns between the month columns?
Or... is there a smarter way to do the conversion than sum across an fill
One approach is to use a macro:
if your empty columns are completely empty you could use this macro. Hit
Alt-F11, Click Insert/Module (check the project window to make sure your
workbook is the active project before...neep help,CSV,import data
My problem is the following:
- I have this contacts in my outlook personal folders
- I've installed MS CRM
- Now I want to add these contacts to the CRM server
- I exported them from outlook2003 as CSV file
- Then I imported them to MS CRM using the import wizard
- While importing the Arabic names appears as unreadable characters
- Any ideas……..plz.....
...how to keep cell rounding to 2 places even when someone else pastes number with more.
At work, i receive spreadsheets of bonuses that can be derived from
formula. however, when i copy and paste the info into our file fo
payroll, it can only be 2 places, otherwise it kicks out an error.
now, i've been here almost a year now and it's been near impossible t
get the people who submit the file to make sure they ROUND the numbe
to 2 places.
i'm wondering if there is something i can do in the cell that when the
paste their number in it, it automatically rounds.
or if that's not possible, if there's a way for me to highlight
column then have it round all t...Date Elimination
I have a worksheet with mainly dates in column A in the format of '25
Aug 2008'. Is it possible with a macro or similar to delete lines
beyond a certain date (2 years hence)? Basically, I'm not interested in
data more than 2 years old.
This would eliminate a lot of data and make for a more viewable worksheet.
Do Until ActiveCell.Value =3D ""
dt =3D Date - 730
If ActiveCell.Value <=3D dt Then
End ...Cut and paste specific data
Have been working on a problem which I think I can now simplify
Cell A1 contains the following:
15 Smith Street Blacktown
How can I cut the last item (Suburb) from the cell and paste it into B1?
Many thanks in advance
Try this in B1:
=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",99)),99))
Please keep all correspondence within the NewsGroup, so all may benefit !
"Father John&quo...eliminating duplicates
I need to elimnate duplicates the following script. I believe what is
creating the duplicates is the provcont table, there are different provcont
ids for each record. Thanks in advance.
INNER JOIN PROVIDER ON PROVCONT.PROV_ID = PROVIDER.PROV_ID
INNER JOIN PROVSTAT ON PROVIDER.PROV_ID = PROVSTAT.PROV_ID
INNER JOIN PSTATUS ON PROVSTAT.STATUS = PSTATUS.STATUS
WHERE (PROVIDER.PROV_PATH = 2) AND ...Data Type Mismatch in criteria
I have the below code. It stops on the If DCount line and says Data Type
Mismatch in criteria expression. Run-Time Error '3464':
I can't figure out what is wrong..
Private Sub LookUpOrder_AfterUpdate()
' Find the record that matches the control.
Dim SID As String
Dim stLinkCriteria As String
Dim rsc As DAO.Recordset
Set rsc = Me.RecordsetClone
stLinkCriteria = "[LeadsID]='" & Me![LookUpOrder] & "'"
'Check table for for item number.
If DCount("LeadsID&quo...Money Keeps Downloading Full Account History
I have archived all my Money Data prior to 1/1/2008 into an old file.
However, Money keeps populating my Account Registers (only for certain
accounts and only once in a while) with the full history from the account.
For example, right now, I have 527 transactions to review with 500 or so
being from before 1/1/2008.
Is there a setting I have set incorrectly? Is there something wrong? Any
way I can fix this?
The basic problem is that you have archived!
If not too far down the track I suggest that you revert to using the
archived file, which is just a copy of your .mny at t...eliminate duplicates
I have a table with 7 different columns of team members on different teams.
I need a count of all team members. How do I query seven different columns
and eliminate dups?
You've discovered yet another problem with denormalized tables. If you have
7 different columns, each containing the same basic information, you have a
If you can't go back and redesign your tables, the easiest approach would be
to create a UNION query that normalizes the data so that it's in only 1
SELECT Member1 AS Member
SELECT Member2 AS Member
FROM MyTabl...eliminate message from append query to eliminate duplicate record.
Create a new query in design view. Add the two tables for which you
wish to compare. When you see the two tables in the design view of the
query click on one of your 2 join fields and drag to the corresponding
join field in the other table (a line should appear) then double click
on fields to add them to the query. For your comparison field Your
going to have to explain how you want to compare the two fields. What
type of field is in table B, and what kind of comparison do you want
to do? You could start with something simple like entering
"ComparisonField:Iif(tableA.field1=tableB.field2,...How to delete some sample CRM Data
I created some test data as Quota, Order, Product. And now
CRM doesn't allow me to delete some of them because of
they became read only data.
Is there any way to delete active or closed quota, Order
etc, and products that in used?
If I delete the user account who is own the records that I
want to delete, what happened?
Any help appreciated.
If you are working into a development or test environment then use SQL to
clear the database. Is the faster way...
If anything stop to work after this, reinstall the CRM. :D
Vin�cius Pitta Lima de Ara�jo
I want to take the average of every other row in my data set and I will be
adding weeks to the data as time moves forward. Is there a relative or
absolute formula that does this?
Thanks for any help.
Similar to columns, but pre-2007 can handle entire column callouts.
Both are array formulas, must be confirmed using Ctrl+Shift+Enter, not just
*Remember to click "yes" if this post ...Outlook keeps hanging on mobile broadband connection
are there any settings that I should check to stop Outlook
from keep hanging when I'm on a mobile broaband connection? All other
internet program work just fine, it is only Outlook that has an issue with
I shoiuld mention that it is Outlook 2007 on XP Pro SP 3. The task bar icon
is permanently set to "Microsoft Outlook is requesting dats from the
server". Messages stay in the Outbox until you kill the Outlook process and
what type of email account?
do you have outlook set to use any internet connection?
-- ...Keep GL and IV in sync between 2 Companies
I have 2 Company databases and I want to keep the GL and IV (Inventory)
tables in sync. Any tools that will do this.
What do you mean "in sync"? Do you need the two companies to match?? Or are
you trying to keep IV in sync with the GL??
Richard L. Whaley
Senior Business Consultant
btw.....there is some good information that might be helpful to you in some
books on Dynamics GP/Great Plains available at www.AccoladePublications.com
"Charlie Zarzour" wrote:
> I have 2 Company databases and I want to keep the GL and IV (Inventory)
> tables ...How can you import data from another database using query ?
I am using SQL Server 2005.
I would like to import data from dbA to dbB for tblMin, but only for some of
the data in tblMin.
Both dbA and dbB have tblMin.
So, in the import wizard, i selected "Write a query to specify the data to
transfer", then I write a query like this:
SELECT * FROM [dbA].[dbo].[tblMin] where symbol = 'ABC'
If I do this, the import wizard creates a new table called Query, and import
the data to Query, instead of importing it to tblMin on dbB.
How can I import the data to tblMin on dbB ?
fniles (email@example.com) writes:...Expanding conditional formating with reference cells changing
I need to extend the conditional formating from a range of three cells to the
whole document. The issue is that for each group of three cells, I need the
reference cell to change. Essentially, I have data in three cells changing to
blue or pink, depending on whether the value in one of the three cells is an
F or and M. I need the reference for each set of three cells to adjust
automatically, instead of manually entering the changes. I have a large table
Can you just select the cells, then copy them. Then select your other cells and
&...Fixed cell references
I want to create a form for purchase requisitions that is a spreadsheet
(using Excel 97) with 4 work sheets.
The first sheet is where the data will be entered, one row per item to be
The other sheets will be fully formatted for printing/faxing and will
include company logo, delivery information, order numbers etc.
What I want to do is for the formatted sheets to reference various ranges of
the data entered on the first sheet.
i.e. the first formatted sheet references the data in worksheet1 cells A1:C20.
The second formatted sheet references the data in worksheet 1 cells A21:C4...Remove All Bill Data
When I first upgraded to Money Plus, I was having performance issues.
Nuking the bills to eliminate the multiple schemas that had built up since
Money 1.0 seemed reasonable, so I performed the operation and rebuilt them.
However, here I am, 4-5 months later, unable to balance my primary checking
account without having Money hang and need to be killed twice during the
process. I can't enter transactions that match scheduled bills without
having Money hang and need to be killed.
So, I nuked the bills again and started over with rebuilding them. Problem
Rather than cleaning up th...Getting data from a formula
Is it possible to extract a value from within a formula?
Let's assume I have a formula in B3 which is "=a3*2". Is it possible to
get the value "3" off the formula in B3 and placed in C3? TIA.
A B C
It would depend on how complex the formula is and how much effort you wanted to
put into the code that would parse that formula.
> Is it possible to extract a value from within a formula?
> For example:
> Let's assume I have a formula in B3 which is "=a3*2". Is it possible to
> get...cross reference two
I'm need to write a formula based on information in a table - as an example:
High Performing Location Market Area Non-Market Area
1 $1,000,000 $750,000 $500,000
2 800,000 600,000 300,000
3 550,000 400,000 200,000
I want to lookup the information in the top row and on the left column, then
multiply by a factor referenced in ...Outlook stealing mailbox data
I'm running outlook on a couple of machines, for some reason one of the
outlooks is pulling all the data from the exchange mailbox and the
other has no data. Where in outllok/mail settings do I adjust it so
that all the mailbox data is held at the server and not deleted from
Also once I've done this I need to recover the mailbox, how would I go
about doing this? There is no backup solution for exchange on our
systems so its a case of recovering from the "fat" outlook.
"Glass" <firstname.lastname@example.org> wrote in message
news:1123603497.812423....F4 no longer automatically creates absolute reference
I select the text of a cell name (A1, for example), hit F4, an nothing
happens. I have to go in and type my $. How do I reset F4 to work?
Did you go into edit mode first (either hit F2 or click on the formula bar with
> I select the text of a cell name (A1, for example), hit F4, an nothing
> happens. I have to go in and type my $. How do I reset F4 to work?
Do you have an F-Lock keyboard?
Change state and see if F4 is enabled.
Gord Dibben MS Excel MVP
On Fri, 4 Jun 2010 12:46:18 -0700, molla =
<molla@di...How to Get Reference of CommandBar in Excel?
i am using VC7 and Office 2003. I want to add a toolbar in EXCEL with some
buttons. i have done that for word already. First i get the reference of the
CommandBar using the following code.
//QI() for application.
m_pWordApp = spWordApp;
hr = m_pWordApp->get_CommandBars(&spCmdBars);
so now when i try to do the same with Excel i receive an error message that
get_CommandBars is not member of m_pExcelApp.
i don't know how to...Exporting the data from the form currently in the foreground
I'm trying to export the data displayed in a form to an rtf file, by
clicking on a submit button on the form with the data I want exported. The
code for the button calls another module with the following code:
DoCmd.OutputTo acOutputForm, , acFormatRTF, FileName, True
The same happens with:
DoCmd.OutputTo acOutputForm, Form_Name, acFormatRTF, FileName, True
Is there an easy way of just exporting the data from a form that is
Or perhaps instead of exporting the form to a rtf file, is it posible to
export the form's recordset? I know that fo...Matching data.
I have two sets of data (2 worksheets)
one set contains a column with 14000 different numbers and a second column
with corresponding values to these numbers.
the second worksheet contains a column of numbers which are some (7000) of
the numbers in the first worksheet.
I need to put the corresponding values from the first worksheet with the
column of numbers in the second worksheet.
The only way I know how is to copy one at a time which is taking a long time.
Does anyone know a quicker way?
On sheet2 in cell B1 enter
Copy down as f...