Convert Rows To Columns In EXCEL. Thanks A Lot.

Nov 18, 2004

I don't know if it is the right place to post this question, but I know many people here are good at both Access and Excel. The question is:

In an excel sheet,
A1: Smith, John
A2: 111 Pine St.
A3: San Diego, CA
A4: (555) 128-549
A5: Jones, Sue
A6: 222 Oak Ln.
A7: New York, NY
A8: (555) 238-1845
A9: Anderson, Tom
A10: 333 Cherry Ave.
A11: Chicago, IL
A12: (555) 581-4914


2. Type the following formula in cell C1:

=OFFSET($A$1,(ROW()-1)*4+INT((COLUMN()-3)),MOD(COLUMN()-3,1))

3. Fill this formula across to column F, and then down to row 3.

4.Note that the data is now displayed in cells C1 through F3 as follows:
Smith, John 111 Pine St. San Diego, CA (555) 128-549
Jones, Sue 222 Oak Ln. New York, NY (555) 238-1845
Anderson, Tom 333 Cherry Ave. Chicago, IL (555) 581-4914

This is exactly what I want. And I can do step 3 manually to copy that formula to each cell. But,
My question is in step 3. If I have thousands of lines, how can I fill that formula to thousands cells? from C1-C1000, F1-F1000.

I really appreciate it. Thanks.

View Replies


ADVERTISEMENT

Convert Columns To Rows

Aug 28, 2006

Hello All,
I have created a table in access with the following column headings:

ID, A, B, C, D, E.

Column ID contains 9 digits text;
Column A column contains email address;
---------------------------------
Column B contains Currency ($); |
Column C contains Currency ($); | Source Fields
Column D contains Currency ($); |
---------------------------------
Column E contains order number;

Now I need to normalize this table to produce a new table with this structure - UNION QUERY is ruled b/c i have more than the three source fields:

ID, Source, and AMT

Where -

ID: would equal to ID;
Source: would be the value of the heading such as B or C or D;
AMT: would be the currency value of each corresponding column.

Help with the Module or any other way to do this will be greatly appericated.

Thank you.

-testcode

View 3 Replies View Related

Convert Columns To Rows

Jun 28, 2007

Hello,
I'm helping someone can help me with a little problem I am having with a peculiar data arrangement in Access. I have the following scenario. My table is set up with the following columns:

AgentID Skill_1 Skill_2 Skill_3 Skill_4
100 51 52 53 54

I want to create a new table that arranges the data like this:

AgentID Skill
100 51
100 52
100 53
100 54

Does anyone have any suggestions how I can do this?

View 5 Replies View Related

Queries :: Crosstab To Convert Rows To Columns

Sep 16, 2014

I have data like:

Code:

SubjectID VisitID VisitNum VisitDate VisitDetail Rspns
1 5 1 2/10/2001 Fever_1 Yes
1 5 1 2/10/2001 Age_1 12
2 7 1 5/08/2010 Fever_1 No
2 7 1 5/08/2010 Age_1 18
2 8 2 9/30/2010 Fever_2 Yes
2 8 2 9/30/2010 Age_2 18
etc.

I need to convert it to a non-normalized wide format, like this:

Code:
SubjectID VisitDate_1 Fever_1 Age_1 VisitDate_2 Fever_2 Age_2
1 2/10/2001 Yes 12
2 5/08/2010 No 18 9/30/2010 Yes 18
etc.

If I didn't have to worry about the VisitDate, a simple crosstab would do the trick:

Code:
TRANSFORM First(MyTable.Rspns) AS FirstOfRspns
SELECT MyTable.PatientID
FROM MyTable
GROUP BY MyTable.PatientID
PIVOT MyTable.VisitDetail;

View 1 Replies View Related

Access Reports: Adding Lines To Separate Rows And Columns Like Excel

Nov 4, 2004

Ive been converting .xls files to Access database files. I would like to use ACCESS to develop the Reports but the client wants the Reports to look like those in EXCEL..eg. where you have lines between rows and columns. If I could give my Access Reports the same look and feel, I could wean these guys off of Excel and into the wonderful world of relational models.

Does anyone have sqlcode or tricks I might use to create the Excel 'look alike' report in Access?

thks in advance...and I will sum.

the ravenman.

View 1 Replies View Related

General :: Access Code To Connect To Excel And Iterate Through Rows And Columns

Sep 28, 2013

I am just starting out learning how to connect Access to Excel. From an Access form, I need to connect to an Excel spreadsheet, and loop through every row/column to look for data that matches a database field data, then populates the associated cell data. I don't want to import the spreadsheet since the user who created it is using it manually, and I am attempting to automate the task. This is probably the most elementary code, but I would like to start out with the best way to do this. What I am finding is code that appears to be much more complicated than I need, for more complicated situations.

View 2 Replies View Related

Modules & VBA :: Union All Query - Transposing Columns To Rows With Variable Columns?

Aug 8, 2013

I was able to use the UNION ALL qry. But, when I have another file (like original2) that does NOT have all the columns listed in the UNION ALL qry, I get a Parameter value box asking for the missing columns when I run the qry.

Example:

original1IDDateGroupChristianJohnnySteve 18/5/2013A1528/5/2013B338/5/2013C2348/5/2013D2358/5/2013E5

original 2IDDateGroupChristianJohnny18/6/2013A212528/6/2013B2338/6/2013C2248/6/2013D22

The UNION ALL qry includes all the possible resources ( includes all the possible column fields Christan, Johnny, and Steve).

When I run the UNION ALL qry with the original2 file, An "Enter Parameter Value" box is displayed with the mssing column name "Steve".

Is there a way to Map the original2 table into a working table with all the columns, or use VBA code to construct the UNION ALL qry to only include the existing columns? My data has variable columns and I'm trying to avoid the parameter popups.

View 2 Replies View Related

Converting Rows To Columns And Columns To Rows

Jul 31, 2013

I am trying to convert a table that looks like the following...

Customer Name SumofSum of Bill Rate Reviewer
000462 John 500 Mike
000224 Mike 900 Jeff

I would like to covert it to....

Customer 000462 000224

Name John Bill
SumOfSum.. 500 900
Reviewer Mike Jeff

I don't think Access has any functions for this but I am not 100% sure. I know this can be done in a Module but I am not sure of how to code it.

View 12 Replies View Related

Totalling Columns & Rows Into Columns

Mar 22, 2007

I'm affraid my confusing topic title is an indicator of how confused I am by this. I can't even understand the variables well enough to fully utilize Access Help or the Search function here...

What I have is a database hat has column headers that look something like this:
Customer_Name, Order_Date, Qty_Ord, Unit_Price, Total_Price

What I'm trying to get is a query output that will have

Customer_Name, Total Orders (in Dollars) for January, Total Orders (in Dollars) for February, Total Orders (in Dollars) for March, etc.

I've been able to set it up to SUM for one month, but not multiples.

I know I'm totally lame (for proof read any of my previous posts) but you guys totally bailed me out the other time I asked a lame question.

Thanks in advance!

View 3 Replies View Related

Queries :: Convert Multiple Rows Of Data Into 1 Row

Oct 11, 2013

I am designing a bead ornament database for my mom to track inventory of beads, inventory of finished ornaments and cost/pricing. I have a table that joins the Item and bead part with the quantity of each bead part needed, it has the following fields: ItemMatlID, ItemID, BeadPartID, QtySo I have multiple rows of ItemIDs for all the BeadPartID & Qtys.Now I need a query with 1 line per Item and all BeadParts and their quantities. However, I need the BeadPart to be a row data and not a column heading. So a crosstab is out, I believe since it wants to make the BeadPart the column heading and not data in the query.

View 7 Replies View Related

Convert Date Column To Two Columns

Mar 25, 2006

Hello.
I don't quite know where to ask this...

i didn't think things through when i initially created the DB, and created a column called dateOfBirth that holds user entered dates as dd/mm/yyyy.
This causes many query issues, and I would LOVE to have this changed.

Is there a script i can run that will take dateOfBirth, determine the month, write the month as Jan/Feb/Mar/... in a column named dobMonth, and take the day value from dateOfBirth (1-31) to a column called dobDay?

BEFORE:

dateOfBirth |
23/10/00 |

After

dateOfBirth | dobDay | dobMonth|
23/10/00 | 23 | Oct |


I don't care about the "year" data, and I already have the dobDay and dobMonth columns set up...


Thanks.

View 10 Replies View Related

Modules & VBA :: Truncate And Convert 1 Row Value To Multiple Rows And Vise-Versa?

Jan 7, 2014

In the attached Database (Access 2007), there are 2 tables - "Source" and "Expected_Output"

1: Table "Source" contains values CONCATENATED in a single row with concatenation string ' OR name = ' With limited knowledge in Access, i tried to perform text-column trying to see if some delimiter could be removed, it messed-up the expected output.

In the attached DB, table "Expected_Output" contains the required output.

2. DB should have the ability to: the values that we obtained in the "Expected_Output" should be converted from multiple rows to single row with concatenations as in the "Source" table Once again i tried using the ' OR name = ' in the query for concatenation and failed again.

View 9 Replies View Related

Rows To Columns

Feb 27, 2007

Hi all,

Is there a way i can transform through a query or vba?
A | B | C
701 70-6
701 70-7
701 70-9
702 70-1
702 70-3
..... etc

transform it to

701 | 70-6 | 70-7 | 70-9
702 | 70-1 | 70-3
703 ....

Thanks in advance

View 3 Replies View Related

Converting Rows To Columns

Sep 20, 2006

I'm filling in for someone who has a strange love for mail merge and now I need to covert employee history from a row to a column for the mail merge.

I currently have a list of employees and every change in position is listed as a new row. Since all employees are not the same some employees have more rows than others. What I need to do is move the history rows into columns. So what I'll end up with is one single row per employees with their history going to the right. Some employees will have more columns than others. I tried doing a crosstab but that will only let me pick one column from the original table.

I have three columns that I need repeated over and over to reflect their work history.

this is what im working with
Name or ID will be the key
Job start date
Job title
department

currently every one of theese fields is in a column but every entry has its own row.

I need a single row for every employee and the columns to repeat to the right to reflect every move.

Can someone help me :D I'm stuck :confused:

I doesnt matter if the output is in something other than access

View 3 Replies View Related

Display Rows Into Columns

Mar 20, 2005

I have a two tables in my database related to employee ratings

Employee
TokenID
Division
Band

PMRating
TokenID
H1

Now this statement
Code:SELECT DISTINCT Employee.Band FROM Employee

This would display me all the band levels within Employee table in each row..

Is there any way I could built a query to the Band names as seperate columns?

i'm doing this so as to display the average H1 rating for each Band level ..that is grouped by Employee.Division..

View 2 Replies View Related

Queries :: Rows To Columns

Mar 26, 2013

I have the following scenario and do not wish to use a crosstab for various reasons. I am combining three queries into one. The last query has information as follows:

Project Type Rev Budget
12345 Debt 100,000
23456 Impact 50,000
34567 Other 25,000

There are hundreds of these line items. The first two queries hold the project information as well and that would be the field that I would use to join each query. Currently the information above is in line form and I need to put it in column form to coincide with my other data. The final result needs to have the following column headings:

Project, Desc, Exp Budget, Expenses, Debt, Impact, Other, Rev Budget

Please note that the first four headings come from the first two queries so I have this taken care of. how do I change the query to put the Type by line item as a column heading and sum the Rev Budget accordingly without creating a table and doing appends or update queries? Can it be done in the query where I am combining the three queries?

View 2 Replies View Related

Selected Columns To Rows

Apr 17, 2014

I have extraction report from calculation program and dont have access to tables. When I import it to MS Access it looks like attached Sheet1. Is there a simple SQL query or VBA code which will allow me to import the data to new Sheet2 table? Fields 1,7&13 will always be the same and I want them to be column names, Fields 4, 10 &14 contains data which will be different each time I delete the old calculation from Sheet1 and import a new one. I would like to be done in Access as I have to import few hundred calculations and store it in Sheet2 table.

View 2 Replies View Related

Take Data From Rows And Move Into Columns.

Mar 11, 2007

Hi, I have data from a form on my website in a text file, that corresponds to each visitor's input, each 13 lines in the form belongs to one visitor, as shown (twice) at the end of this message.


What I would like to do is have each visitors inputs translated to ONE row, with 13 columns/fields each. It could be appended to the same table or preferably generated in a new one. Note, there are no blank fields, some won't have data after their title, i.e. addy_line_2: is often blank, but at the very least, addy_line_2: or another field name is always there.

It would ROCK if I could also automatically take the name of each field out, i.e. each name is continuous characters up to the : (colon) ...

Thanks in advance, my Access knowledge is obviously limited, I'm sure this is fairly simple!

The fields:

Phone: 213-555-1212
Submit: Continue
addy_line_1: 1000 Melrose Place
addy_line_2:
badge:
city: Los Angeles
email:
homegroup:
name:
program:
state: CA
volunteer:
zip:
Phone:
Submit: Continue
addy_line_1:
addy_line_2:
badge:
city:
email:
homegroup:
name:
program:
state:
volunteer:
zip:

thank you thank you thank you thank you thank you !!

View 1 Replies View Related

Query To Total Columns AND Rows

Jun 2, 2005

Is it possible to total columns in a query? Right now, I have a query that produces the following column counts, but I'd like to total Pending, Overdue, etc. This data is being displayed in a subform.

Process Pending Overdue Total
-------------------------------------
Engineering 1 2 3
Procurement 0 6 6
<etc>
-------------------------------------
TOTAL 1 8 9 <- this is the line I want to add

Here's what the query (qryStatusRptB) looks like thus far:
Field: Process
Table: tblProcesses
Total: Group By

Field: Pending
Table: qryStatusRptA
Total: Sum

Field: Overdue
Table: queryStatusRptA
Total: Sum

Field: TotalRecords: Count(*)
Table:
Total: Expression

Can someone explain how to get the TOTAL ROW in here? (I can do it via another query, but that won't work since the data is displayed in a subform. I've tried crosstabs without success.)

Many thanks,
Christine

View 2 Replies View Related

Merging Data From Columns Into Rows

Jan 19, 2006

Hi everyone,

I can't get my head around this so I'm looking for some help if possible please, there are two questions, the first is:

When in the query, I want the criteria for the date selection to be a question, ie. "[Week Start Date?]" but I want the actual criteria selection to be from the start date plus 5 days, the only way I've done that so far is to do ">=[From?] and <=[To?]", which uses two questions and I don't seem to be able to do ">=[Week Start Date?] and <=[Week Start Date?]+5" which seems basically correct, but I expect I'm writing it incorrectly (basic access knowledge I'm afraid :( )

The second question (after I've got the 5 day date criteria sorted) is that the query produces a table that shows basically the following:

Name Store Date Visited
Tom Bury 18/01/06
Tom Bury 19/01/06
Tom Diss 20/01/06
Dick Thetford 18/01/06
Harry Diss 20/01/06

The query is based on a part week period with the starting date ALWAYS a Monday, so no more than Mon-Sat will appear, I want to put the information into a table or query, so the result ends up as:

Name Store Mon Tue Wed Thu Fri Sat
Tom Bury 18/01/06 19/01/06 'Blank' 'Blank' 'Blank' 'Blank'
Tom Diss 'Blank' 'Blank' 20/01/06 'Blank' 'Blank' 'Blank'
Dick Thetford 18/01/06 'Blank' 'Blank' 'Blank' 'Blank' 'Blank'
Harry Diss 'Blank' 'Blank' 20/01/06 'Blank' 'Blank' 'Blank'

*Where the blanks are simply left empty, rather than putting in the word 'blank'

Essentially converting the "[From?]" (as stated earlier) or "[Week Start Date?]" to Monday, that date + 1 to Tuesday, etc, BUT putting multiple dates relating to name and store criteria onto one record :eek:

Beyond me I'm afraid, any pointers would be seriously appreciated, I expect I'm approaching the problem from the wrong angle.

Regards
Tony

View 1 Replies View Related

Merging Data Columns Into Rows

Jan 19, 2006

Hi everyone,

I can't get my head around this so I'm looking for some help if possible please, there are two questions, the first is:

When in the query, I want the criteria for the date selection to be a question, ie. "[Week Start Date?]" but I want the actual criteria selection to be from the start date plus 5 days, the only way I've done that so far is to do ">=[From?] and <=[To?]", which uses two questions and I don't seem to be able to do ">=[Week Start Date?] and <=[Week Start Date?]+5" which seems basically correct, but I expect I'm writing it incorrectly (basic access knowledge I'm afraid :( )

The second question (after I've got the 5 day date criteria sorted) is that the query produces a table that shows basically the following:

Name Store Date Visited
Tom Bury 18/01/06
Tom Bury 19/01/06
Tom Diss 20/01/06
Dick Thetford 18/01/06
Harry Diss 20/01/06

The query is based on a part week period with the starting date ALWAYS a Monday, so no more than Mon-Sat will appear, I want to put the information into a table or query, so the result ends up as:

Name Store Mon Tue Wed Thu Fri Sat
Tom Bury 18/01/06 19/01/06 'Blank' 'Blank' 'Blank' 'Blank'
Tom Diss 'Blank' 'Blank' 20/01/06 'Blank' 'Blank' 'Blank'
Dick Thetford 18/01/06 'Blank' 'Blank' 'Blank' 'Blank' 'Blank'
Harry Diss 'Blank' 'Blank' 20/01/06 'Blank' 'Blank' 'Blank'

*Where the blanks are simply left empty, rather than putting in the word 'blank'

Essentially converting the "[From?]" (as stated earlier) or "[Week Start Date?]" to Monday, that date + 1 to Tuesday, etc, BUT putting multiple dates relating to name and store criteria onto one record :eek:

Beyond me I'm afraid, any pointers would be seriously appreciated, I expect I'm approaching the problem from the wrong angle.

Regards
Tony

View 2 Replies View Related

Transpose Weekday Columns To Rows

Dec 18, 2007

I have a table with 8 columns titled - Destination, Mon, Tues, Wed …to.. Sun. This is shown as "OriginalTable" worksheet in the attached example workbook. I want to create a new table from this original table that looks like "NewlTable" worksheet in the attached example. So I will have a new table that has 3 columns titled - Destination, Day, Weight. Weekdays will become rows so each destination will have 7 rows, one for each day of the week. The measure will be listed against each day of the week. Can someone please look at the attached example for me and help me with this.

Many thanks in advance.

View 2 Replies View Related

Changing Data From Columns To Rows

Jun 30, 2006

Hi all,

I have a query which runs off a table. I have the following Fields as columns in query: WeekID, A, B, C, D, E

For each week, the letters correlate points given. So for week 1, "A" could have 1 point, "B" could have 3 points, etc.

I want to run a Query that will show A, B, C, D, E as rows like the following:

Letter_Week 1_____Week 2_____Week 3
A_______1_________5___________3___
B_______3_________4___________2___
C_______2_________1___________7___
D_______6_________3___________3___
D_______3_________1___________4___

I can't run a crosstab because it will only crosstab values within all of A, B, etc.

Any idea guys? Thanks in advance, as always.

Caliboi

View 3 Replies View Related

Queries :: Turning Rows Into Columns?

Jan 20, 2015

I have a file structured as listed below. I need to pivot Patient Id, Patient Name, Send Reason, Provider Name and Visit Number into columns and then insert the values into the corresponding columns.

My data set contains about 1,000 rows, so I cannot simply paste special in Excel.

Patient ID 111111
Patient Name Doe, Jane
Send Reason Cannot resolve provider
Provider Name Doe, John
Visit Number ABCD#F1234567

I have tried Pivot and Transform, neither of which seems to work.

View 13 Replies View Related

Table With Three Fields - Rows To Columns

Jan 28, 2012

Rows to columns;

I have a table with three fields

ID,Desc,Code
1,aaa,code1
2,bbb,code2
3. aaa,code3
4, bbb,code4

I need a query creating these two columns:

Desc,Code
aaa,code1;code3
bbb,code2;code4

how can I do this?

View 1 Replies View Related

Changing Rows To Columns And Visa Versa

Aug 21, 2007

I work at a college and have created a report that automatically retrieves the instructors name, class number, class name and the scores that instructor received on his evaluations. Right now the report is showing the questions of the evaluations on a row at the top with the numbers underneath, like this:

Code:Instructor Punctual Knowledgeable RespectfulBio1 3.4 3.7 2.8CH393094Chem1CL349750 2.4 3.8 3.7My boss is wanting it to look like this:Instructor Bio1 Chem1 CH089084 CH90750Punctual 3.4 3.2Knowledge 2.8 3.7Respectful 3.8 2.8

But no matter what I do I can't get it to change to that. Even though I've set up all the things to look like that I can't get it to work.

Any suggestions?

Oh yeah, I'm using Access 2003.

View 7 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved