Combine multiple rows into one row with multiple columns
I have a table set up so that there are three columns: StudyID,
DrawDate, and Value. StudyID and DrawDate are the primary key. I
want to create a table from this one that has only one row for each
StudyID so that it would go from:
StudyID DrawDate Value
StudyID DrawDate1 Value1 DrawDate2 Value2 DrawDate3
Is there a way to do this?
"Elysia Larson" <email@example.com> wrote in message
> I have a...Column to Rows
I want to convert my data from one column into rows.
I have my data set up now as follows:
555 Main St.
345 Happy Dr.
354 Oak Pl.
I want it to be displayed into 4 separate columns as follows:
John Smith $3200 555 Main St. 95111
Jane Jones $5500 345 Happy Dr. 93434
Jack Clark $2300 354 Oak Pl. 95343
Don D.'s Profile: http://www...Charts switch from 'Series in Rows' to 'Series in Columns'
I use VBA to create charts in Excel 2003, but find that sometimes the
Charts switch 'Series in Rows' (intended) to 'Series in Columns' (not
intended), even if I have specified 'Series in Rows'. This happens
intermittently, and I am not sure what I am doing wrong. I do save
the workbook as Microsoft Excel 97 so that a user with Excel 2000 or
Excel 2003 can use the workbook.
Thank you for any suggestions.
Hard to tell if you keep the code secret.
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
"Peac...Problems with re-setting the last active cell in an Excel workshee
I am trying to re-set the last active cell on an Excel 2002 worksheet (in
this particular sheet it should be cell DA197). I have used both the methods
described in the Knowledge Base article (deleting rows and columns and
re-saving; and the Excess Format Cleaner add-in).
Deleting the rows and columns does not work; using the Excess Format Cleaner
does not work either and it then also hides the rows from 198 to 65536 - but
does not do the same for the columns.
I have checked that there is no protection on the worksheet.
Has anyone else come across this problem and if so can you please ...Right column spilling onto 2nd page
I bought a template from a vendor and saved it as a new template with my
version of excel. The link to that file is:
http://www.utahhousevalues.com/lgfiles.cfm. The vendor is not able to help
me figure out why the right column is spilling onto a second page. There
are 49 total pages and about 25 of them spill the right column onto a second
page. They claim each page is identical and it should not do that. I'm
using Small Business version of Excel 2000 with XP Professional SP-1
If the vendor can't tell you w...copy sheet1 from bookA to bookB
If I copy a sheet with formulas from bookA to bookb, all formulas than point from bookB to bookA
How could I avoid that
Same for copy sheet1 to sheet2
Once you have copied the sheet across and the formulas
are referring to bookA...
Highlight the entire new sheet, and do a replace (Ctrl H)
Find What : [BookA]
Replace With :
Leave Replace With blank and hit replace all.
Obviously you need to have a sheet called sheet1 in your
Check you have broken all links by going Edit / Links...
links should be gre...Replace Cells with Column names in functions?
I have a # of fairly long/complex cell functions that get hard to debug
because there are also a lot of rows.
Is there anyway to change display so it replaces the column name e.g.
CustomerName is a defined name range for BT1204
Find & Replace
Find what: BT1204
Replace with: CustomerName
> I have a # of fairly long/complex cell functions that get hard to debug
> because there are also a lot of rows.
> Is t...VBA to call file based on date
I wonder if anyone can help me with a VBA routine to check for the presence
of a specific file.
The file is saved on a dialy basis in the format "userfile_run_YYYYMMDD.xls"
i am seeking help with a vb code that will check if today or any specified
days report is generated. i guess excel can use the date format to read the
specified location for the file.
Thank you in advance for your help.
.....Coming from Where I'm from.
Dim sFile As String
sFile = Dir("C:myPath\userfile_run_" & Format(Date,"yyyymmdd") & ".xls")
If s...Excel worksheet total up to show in another worksheet
I have two worksheets. The first is the nice printable page. On the second
page I just total up my receipts. How do I get those totals to show on
Worksheet 1 in a cell called Total?
Just say your totals to be added are in A2:A120 in Sheet 1 then
hope this help
nika's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1536
View this thread: http://www.excelforum.com/showthread.php?threadid=31615
...extract info from cell, then count
I have a 2-part question:
(i) I have 1000's of e-mail addresses but want to extract the countr
from the e-mail i.e. firstname.lastname@example.org, where de (Germany) is needed. How d
I isolate the ".de" (and others eg .fr, .edu, .com etc etc)
(ii) Having done the above, I then need to do a count. Rather than us
COUNTIF and include the code for every country in the world, is ther
any other way of counting? I guess a Pivot table?
Message posted from http://www.ExcelForum.com
=MID(A1,FIND("^",SUBSTITUTE(A1,".","^",LEN(A1)-LE...graphing counted time stamps
Is it possible to generate a graph of counted timestamps? I am importing into
Excel a log file and get one column of time stamps, some time stamps are 1
time , or multiple times. The idea is that for each one time stamp on the
x-axis the y axis shows the counted amount of timestamps for this time. I
assume this should be a common problem, but I could not find the answer for
Any help appreciated, Wolfgang
Create a PivotTable and PivotChart with the timestamp as the row field
and the data field. Make sure Excel uses COUNT(Timestamp) as the
statistic it shows and not SUM(,,,...another query question
I want to apologize ahead of time for being a novice with MS Access
and VBA. I desperately need help with 2 queries that I am trying to
put together. I want to thank anyone that can help me out with this
I want to put a select query(Query1) that uses one table and the
criteria would be [Enter the Event Number]. When an event number is
entered it will give me the Event Number, Event Name, Event Date,
Event time, and House Size. Next, I need to create a 2nd
query(Query2) that pulls up the records from another table by the
Below are the tables and fields that I am u...Row Grand Totals in Pivot Tables?
I'm working in Excel 2007 and I can't seem to see my row grand totals in my
pivot table. I can see the grand totals on the columns, but no rows. Any
See if the article at
does what you want.
> I'm working in Excel 2007 and I can't seem to see my row grand totals in my
> pivot table. I can see the grand totals on the columns, but no rows. Any
Yeah, that is what I would expect it to do too. However, I set that option
and don't receive the total.
...Date last edited
I would like to enter a field into the footer of an Excel document that
automatically displays the date and time the document was last edited. This
is possible in Word and Visio, but I can't find a way to do it in Excel. Is
Private Sub Workbook_BeforePrint(Cacel As Boolean)
.ActiveSheet.PageSetup.LeftHFooter = "Last saved on: " & _
Format(.BuiltinDocumentPropert�ies("Last Save Time"), _
"dd mmm yyyy")
goes in the ThisWorkbook code module.
Bob P...how do I sum only visible data in a column
I have some rows manually hidden, Please is there a formula I can apply to
return the sum of the unhidden data contain in a column?
If you don't want to use VBA (create a UDF), you could review the following
In any case, there is a fundamental issue with both approaches: since
hiding/unhiding rows doesn't trigger any event, such event will have to be
forced from time to time, or maybe you can leave with the formulae updating
their result in the next recalculation...Copy/paste Function
When I try to copy a cell or a worksheet from one worksheet to another, it
only gives me the option to create a picture - I can no longer paste the cell
or worksheet. This is Excel 2007
...Move downloaded transactions to another account?
For some reason, the downloaded transactions for one account have appeared
in another account. I don't see any way to move these to the correct
Is this possible to do in Money 2008?
in 2007 right click on the transaction and copy and paste to different
account, not sure about 08.
"Noozer" <email@example.com> wrote in message
> For some reason, the downloaded transactions for one account have appeared
> in another account. I don't see any way to move these to the correct
> I...Excel Row as Header
I am making a table and the top row has all the headings for each column. I
want this row of headings to appear on each page, since the table extends to
3 pages and users will be adding rows frequently. Is this possible, and if
If you are refering to printed pages, then:
File > Page Setup
Then select the 'Sheet' tab and enter your heading row where it says
'Rows to repeat at top'.
I use XL2000. Other versions might be slightly different.
Regards - Dave.
And if you're referring to the screen:
Select cell A2
Ki...Adding up numbers from columns.
How can I sum up multiple columns while not letting each cell
"associated values" sum to a number greater than one specified.
if I have sets of colums:
I want to know what 3 combination of numbers in the left 3 colums giv
me the highest value...WITHOUT letting the correspoding numbers in th
right hand column be greater than, say 10.
Message posted from http://www.ExcelForum.com
...Getting Data from another sheet? #3
WHere do I save it to
bludovico's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1480
View this thread: http://www.excelforum.com/showthread.php?threadid=26722
The manual is not ready yet (First version of the Add-in)
Save the file where you want on your PC.
Then in Excel go to Tools>Add-ins.. Browse to the file..OK
You see it in the list now (Select it)
Regards Ron de Bruin
"bludovico" <bludovico.1dribg@excelforum-...Result of one combo box, affecting results of another...
Just out of curiousity...
Is there a way to have what the user selects in one combo box on a
form, affect what is available for the user on another box!?
For instance... Box A has 8 different addresses.. Within those
addresses there are 12 different building ID locations... Instead of
having all 12 building locations for each address always appear in the
combo box, If the user picks building 4, is there a way to just have
the building ID options for building 4 showing in combo box B?
Use your favorite search engine.
Use "Cascading Combo boxes" for search terms.
Reg...Sum a table of columns & rows
I have a spreadsheet of 154 Rows (all unique project numbers in numerical
order) and 9 columns of account numbers (some are similiar and some are user
entered, therefore there could be 'blanks' with no data in them).
I am trying to create a table that will only give me the project number if
there are dollars in one or more of the columns. This would be used for data
entry (and that is why I would like to have the columns summed up - to remove
Any ideas? I have given a brief example below:
F, G, &am...SOP Invoice: Custom footer based on logged in user or customer cla
I've been asked to determine if its possible to conditionally print footers
on SOP Invoices to indicate the contact info of the person responsible for
the client company.
I was looking at either using the current logged in user or the class of the
customer to determine which info to include.
Can anyone point me to a KB article or some other source that can show me
how to do this?
Unforunately the user-defined fields in the customer record have already
been dedicated to something else.
You can attach some VBA code to the report and use the
RetrieveGlobals.dll supplie...Sum on top of stacked columns #2
Is it possible in some way to add the sum of series on top of each
column in a stacked column diagram?
I am often using this feature in ThinkCell for my powerpoint slides.
However, need to insert a stacked column graph from Excel 2007 into
Word 2007. Currently, I've been using a text box with a reference.
However, the reference in a text box is static, which makes it a
source for error when inserting or deleting rows in Excel.
You can add a new series with the total sum and then:
Change the chart type to line ONLY for this series.
Format the series without lines or markers...2 questions one on list/combo boxes and the other on "atomically" hiding columns or rows.
I followed most of the post recently on the subject of list and combo boxes.
From what I can tell I need to create a list but I need to have the list
find the item and than a price associated with that item and than it's cost.
Is there some where that I can find away to do that?
The other question on the opening page of my quote sheet I have by columns
the floors (4) of the house and under those a list of rooms (these are the
headings of the rows) that are on each floor. In the following columns I
have the my wiring combination. I have all this linked to other worksheets
that have simil...