How do I convert some data in the row to columns?
I have an issue that I need to resolve.
I'm currently getting data in a row format, but I need to convert some
of the data into columns, for importing into Access.
Item Description Jan 05 Feb 05 Mar 05 Apr 05
ABC123 Widget A 100 250 175 210
XYZ123 Widget B 50 75 100 76
To convert to........
Column1 Column2 Column3 Column4
Item Description Date Qty
ABC123 Widget A Jan 05 100
ABC123 Widget A ...format columns to collapse and expand in excel
I already did this in an old excel worksheet but I CAN'T remember HOW I DID IT.
The result is a (plus) + and a (minus) - sign at the top of the worksheet in
row 1 at the location where you want your selected columns to either expand
I need to once again implement this handy formatting trick but for the life
of me remember how I did it....so frustrating!
Anyone out ther know how to do this?
Move your cursor to the top of the column you wish to AutoFormat.........say
it's column C, then ease the cursot to the right to the separation between
Column C and column D and ...Matching the colors Column Charts and Pie Charts
I have a file that contains three graphs. Two pie chats, one charting
revenues and the other charting expenses, showing the contribution from
each division of a department. At the top of the page i have a column
chart graphing revenues and expenses together. The source data for all
three charts is coming from the same data. I am trying to find a way to
create a column chart that will use a different color for each division,
like the pie chart does, instead of one color for revenues and one for
Is this Possible?
--------------------------------------------...Column Alignment Problem
My "message" column disappeared, and when I added it back
into the view, the text is aligned to the right and will
not adjust back - even tho I reset the alignment to the
left! Now it will not sort the "nmessage" column and half
of the text of messages names is out of view. Help?!
Try resetting the view -- View | Current View | Define Views, select the
view that's messed up, and click Reset.
MVP - Outlook
*** Messages sent to my e-mail address will NOT be answered -- please
reply only to the newsgroup to preserve the message thread....Combining IF and COUNTIF based on two columns
Here is what I'm trying to do, I have two colums with data, the first column
will have one of four letters (A, B, C, D) the second column will have one of
two numbers (0, 1). I need to get 6 different counts:
COUNTIF column A range A1:A250 is A
COUNTIF column A is range A1:A250 B
COUNTIF column A is range A1:A250 C
COUNTIF column A is range A1:A250 D
COUNT column A ONLY IF column B range B1:B250 is 0
COUNT column A ONLY IF cloumn B range B1:B250 is 1
I can do the first 4 calculations pretty easy but am having a hard time
figuring out how to do the last two calculations. Can anyone h...Text box bound to non-default combo box column
I have a text box that I would like bound to a drop down control. It
is easy enough to set the Control Source to equal the drop down's
default column value (i.e. "=[ComboBoxName]"), but the combo box has
three columns, and I want it bound to a non-default column.
I have tried "=[ComboBoxName](2)" and "=[ComboBoxName(2)] where (2) is
the column that I want to bind to with no luck.
Any info is greatly appreciated!
> I have a text box that I would like bound to a drop down control. It
> is easy enough to set the Control Source to equal the d...Comparing dates in columns from linked table
I'm new to Access/SQL and have a problem with a query that I just cant seem
to be able to solve.
So, I have an Access database containing client number, security name and
date, that information has to be updated every month, the source for the
update being an excel file that I link to the Access database. But I dont
want to overwrite on the existing data so what i wanted to do was add the
current month's data, when it's not already in there.
What I did was an insert query which inserts the data from the excel file to
the table in the Access database only if the c...Hiding Columns with Zero Data
Is is possible when creating a chart to ignore columns that have no data in
them. Such as a Data Table with 31 Days but not all days have data against
Maybe Jon Peltier's squeezed columns example can help.
> Is is possible when creating a chart to ignore columns that have no data in
> them. Such as a Data Table with 31 Days but not all days have data against
> them. Ta
Andy Pope, Microsoft MVP - Excel
...Sum total hours worked in excel
I want to add the total hours worked in excel that I book for the month for
A B C
Start Time End Time Total
08:00 16:00 08:00
08:00 14:30 06:30
08:00 15:00 09:30
It stop at 23:59 and I want it to go on like a total sum, wat formula must I
use to do my total sum calc for my time? please help
You should format the cells in column C as [h]:mm - that way they will
not wrap round above 24 hours, but ...Making Text Fit Flush In Columns
Hi everyone, is there a simple method to ensure that the text in my 9.75 H x
3.66 W text boxes fits flush top to bottom so each column of text has perfect
alignment? My master page creates my margins but I am then inserting text
boxes into these columns. (is this okay or maybe what is causing the
misalignment? Thanks in advance.
What version Publisher are you using?
Have you tried using the Baseline guides? View, Baseline guides. The text size
is formatted in the Arrange menu.
Format, paragraph, check align text to baseline guides. You will have to do this
for every text box.
Why are...Total in Footer
I'm new to access and have a question regarding the footer. I created a text
box and inserted a basic equation for the the total cost of the order, =Sum(
[UnitPrice]*[Quantity]), however it is not appearing on the form. The
visibility property on the footer is set to Yes.
Please let me know what I am missing -
On Mon, 26 Apr 2010 19:58:53 GMT, "TheLee" <u59624@uwe> wrote:
>I'm new to access and have a question regarding the footer. I created a text
>box and inserted a basic equation for the the total cost of the order...change color of columns depending on value
use 'Format - Conditional Format' for this. See:
"van Velzen" <email@example.com> schrieb im
And just in case you meant chart columns have a look at
Frank's so prolific he may have missed the NG you posted in <vbg>
Frank Kabel wrote:
> use 'Format - Conditional Format' for this. See:
> ht...how to convert multiple columns of data into one single column?
as subject, e.g.
A B C D
Mary Peter Paul David
John Nancy Lucy Ken
Alice Annie Danny Jane
try the following formula on the second sheet in cell A1
and copy down
"���T" <,> schrieb im Newsbeitrag
> as subject, e.g.
> A B C D...Rows to columns
Happy New Year to all of you.
Here is my big problem:
I have developed a relational database using access 2003 with the following
-T_Polygons (PolygonID – text 6 – Primary key)
-T_Data1 (PolygonID and SpeciesID – Primary keys)
-T_Data2 (PolygonID and SpeciesID – Primary keys)
-T_Data3 (PolygonID and SpeciesID – Primary keys)
As you realize, each Polygon has several records to the other related tables.
I want to run a query (perhaps) and give me as column A the polygon ID and
the following columns (0-38 for each related table, or just the 3 first
records) to be t...Dates posted within a standard column chart
I have a regular column chart with dates at the bottom, and the columns are
the # of cases made per week. I'm trying to include the dates the Case
specifications were changed within the chart. I only want to have a point
within the chart showing the date it was created within the dates already
showing. For example, I have the dates of 10/1, 10/6, 10/13, and 10/20 on
the X-axis. There are four columns per date. The dates of the specification
changes are 10/9, 10/14, and 10/15. I'd like points showing on those dates
with Version 1, Version 2, and Version 3 marked. Any ideas...Merging columns? #2
::Thanks for responding, Myrna.
There is only one row per person-- and no, there is not a blank ro
between each filled row.
Carolina78703's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1562
View this thread: http://www.excelforum.com/showthread.php?threadid=27225
If you have only one row for each person, I guess I don't understand what you
want to do. Combine the entries in A1, B1, C1, and D1, with a space between?
=A1&" "&B1&&...Excel Column Headings Compatible with Outlook to import.
When I try to import an excel file into Outlook - I get the message to go
back into excel and name the ranges. I understand highlighting the area -
going to insert/name/define/add/ok. Here's where I'm having difficulty. I
receive a contact file with names and phone numbers. At the top of column A
it says Name and below in the same column is the list of names. I right
click on the first cell in the column and choose "pick from list" - then a
drop down window apprears with a list of the names in the column. When I go
through the same process with column B which has...look up and match exact data is adjacent column
I am trying to import notes from a current data base program, which I
exported into excel, into a new database program which requires me to match a
unique case senstive ID (alpha & numeric) to an list of corresponding account
numbers from the existing database. To be clear, both databases contain the
same list of account numbers, but only the new database has the required
unique case senstive ID, which is why I need to match it with the old
I had partial success using the Lookup function, but it would not provide an
A sample of my work can be seen below....Column Headings #8
I have received a spreadsheet where the column headings are numbers and the row headings are also numbers. Can the column headings be changed back to letters? If so, how can I do this?
There are instructions for changing this in the Excel FAQs here:
Choose Tools > Options and select the General tab.
Remove the check mark from 'R1C1 reference style'
> I have received a spreadsheet where the column headings are numbers and the row headings a...How to create a stacked, staggered column graph w/ 2 y axes
Hi everyone. I searched the forum and couldn't find any answers to
this. I'm trying to compare two years worth of data using the
following information (w/ Excel 2003):
Stacked: A vs B
Staggered: 2006 vs 2007
2 Axes: % of A to B 2006 vs % of A to B 2007
Anybody have a clue on how to do this? I can create a stacked graph of
2006-2007 data w/ a y axes for % of A to B; but it would make much
more sense if i could compare the years side by side rather than
linearly to look for seasonal trends. any ideas?
What about using day of year trend chart to compare 2006 versus 2007?
H...Subtable that automatically expands with totals on the top
I would like to create a little worksheet to do some calculations on
some sample data. I would like a section of the worksheet to act like
a "subtable" with one row for each set of sample data, headings across
the top, the ability to add new rows at the top, and with the totals
also at the top.
For example, if the subtable starts like this:
n Head1 Head2 Head3 ...
Tot total1 total2 total3 ...
1 data11 data12 data13 ...
I would then like some way (ideally a function key or keyboard
shortcut) to add a row BEFORE row 1 so that it looks like this:
n Head1...Combo Box
AFTER a user has choosen a row from a combo box, it appears that the combo
box automatically display the first printable column.
Is there a way to display someother column than the 1st printable column?
Sometime I would like to print the 2nd or 3rd column. Is there a way to do
> AFTER a user has choosen a row from a combo box, it appears that the
> combo box automatically display the first printable column.
> Is there a way to display someother column than the 1st printable
> column? Sometime I would...To find out the total number of hrs worked in a week
I have two column in an excel sheet
A1 and B1
A1 cell contain total number of hrs in a day worked ie 6:30 (hh:mm) format
B1 cell contain number of days ie, 5
Please how can find out the total working hrs ie 6:30*5 ie total hrs worked
is 32.30 hrs .
Please give me a solution to arrive this answer
then (IMPORTANT) custom format the cell
If you need to calculate a total amount using an hourly rate use
and format as currency or number
"pol" <firstname.lastname@example.org>...sum function with range dependant on date
This is a multi-part message in MIME format.
I have the following data
In January, I want to sum all four rows (50 through 20). In February, I =
only want to sum the last three rows (40 through 20). In March, 30 =
through 20 and in April 20 through 20. I want to tell the sum function =
which month to start by referring to a cell with the month (i.e. 2/1/06 =
for the 40 through 20 sum).
I cur...Macro to change Chart Range when inserting a column
I have created a workbook to use as a weekly reporting tool for different
sites across the country. It contains a "Report" sheet and a "Data" sheet.
There are 4 charts embedded into the Report sheet. I have set-up the Data
sheet to have the data titles in Column A, a 12-week summary in Column B, and
the weekly data begins at Column C. For the 12-week, I used the formulas
=sum($C$1:$P$1) and on the Chart series, I used =Data!$C$1:$P$1. I designed
the sheet so a center would insert a column at Column C each week for the
most recent data. Unfortunately, everytime th...