Convert The Data Shown In Table 1 To Table 2 Without Rearranging The Columns And Rows

Sep 11, 2009

Is there a function to convert the data shown in table 1 to table 2 without rearranging the columns and rows? because i don't want to use TRANSPOSE. I want a function, somthing like SUMIF with OFFSET or INDEX and MATCH or any other function.

Table 1

Team 1Team 2Team 3Team 4Team 4Team 5Team 5ABABCity 12531642City 231173705City 367891125City 436251348

Table 2

City 4City 2City 1City 3Team 4BTeam 2Team 5ATeam 4ATeam 1Team 3Team 5B

View 2 Replies


Rearranging Data In Table Without Calculating Anything?

Jan 6, 2014

I have a csv file that includes several runs of a test per day on 5 different parameters. I need to rearrange this data to show the results from each run on each date, but arranged in a different way. I've attached an .xlsx file that best describes what I have

View 8 Replies View Related

Macro For Rearranging Data Table?

Nov 28, 2011

I have a data table which looks like this:

1 2 3 4 a b c
5 6 7 8 d e f

And it should be converted to look like this:

1 2 3 4 a
1 2 3 4 b
1 2 3 4 c
5 6 7 8 d
5 6 7 8 e
5 6 7 8 f

So basically, the data in the last columns should be transposed, and the data in the first columns need to be copied in the cells of the new rows.

View 1 Replies View Related

Error In Filtering Table - Additional Rows Shown / Rows Hidden When Filter Removed

Mar 4, 2014

I have an excel file with a table in it. It contains 2051 records (attached). This is just a sample, the original file has around 30,000 rows.

When I start using filters, I run into problems:

Step 1: Filter by Unit, condition (e.g.) Unit_23
Excel shows in the status bar the following message: 437 of 2050 records found.

Step 2: If I scroll to the bottom of the table, the row numbers are colored in blue (normal for a filtered list) however the last row is not colored and it actually should not be shown since its unit is not what I filtered for (its unit is Unit_25)

Step 3: Clear the filter of Unit

Step 4: Sometimes (depending on what I filter for), one or more of the bottom rows are hidden!!!

Attachment 301726

View 4 Replies View Related

Rearranging Values In Table

Jun 25, 2009

I have table with duplicates values on the left side. Table has three columns and in each row is value only in one column. It looks like this:

101 22
101 3
110 6
110 2
110 3
112 12
114 8
114 6

I need to get rid of the duplicates in first column and save values for same number in first column in one row. Like this:

101 22 3
110 6 3 2
112 12
114 8 6

I think only macro can do this.

View 14 Replies View Related

In A Table Include Data In Columns Into Existing Rows

Jun 7, 2012

I have a table in which the rows represent cities and the columns represent certain characteristics/ attributes a city can have. In the cells every city & attribute pair receives a "grade"

City EatingBeachSki
Barcelona 210
Chamonix 002

I would like to transform this so that every row represents a city & attribute pair and includes its corresponding grade.(I want to do this in order to be able to add more information about city- attribute pairs rather than about cities only)

City AttributeGrade
BarcelonaEating 2
BarcelonaBeach 1
BarcelonaSki 0
ChamonixEating 0
ChamonixBeach 0
ChamonixSki 2

I am aware that I can use a pivot table to sort out all the pairs with a specific grade. and then I could paste this into a new table. But my data base (number of cities x number of attributes) is huge and I was wondering if there is no automatic process to make this transformation.

View 4 Replies View Related

Vlookup Table To Lookup Data In Columns And Rows

Apr 28, 2007

I have a lookup table which needs to be able to retrieve data from columns as well as rows and not sure if this is possible.

My data is set up with an ID number, financial year and data across the columns e.g

ID F/Y Apples Pears
9999 2004/5 5 4
9999 2005/6 7 8
9881 2003/4 5 3

My lookup table will have the ID number field which will be manually entered to lookup the information on the data sheet. e.g

ID 9999
FY Apples Pears
2004/5 5 4
2005/6 7 8

I can't work out a way to get excel to only give me the number of apples and pears for that year for that ID number. Not all ID numbers will have the same number of financial years so I may need to use VBA.

View 7 Replies View Related

Transfer Data Table 1 To Table 2 Change Rows To Column?

Mar 13, 2013

Table 1

Table 2

Sr. No


View 1 Replies View Related

Convert Table Into Three Columns

Aug 27, 2009

I am trying to convert a table into three columns so that I can use the data in a vlookup.

View 8 Replies View Related

Convert Layout Of Excel Table To Make It Suitable For Pivot Table

Mar 27, 2012

I have a very large excel data file, which I want to analyse using pivot tables. The problem is that while most of the columns are headed with the variable name (e.g. country) and have the list of variables displayed under that heading for each observation (e.g. Italy), the years are spread across the columns - i.e. the heading for column X is not "Year", but is 2003, with the next column being 2004, etc.

Is there a quick way I can re-arrange the data so that the layout is consistent and so that I can use it for pivot tables? I have way too many observations to do this by hand.

View 1 Replies View Related

When Receive More Than Order As In This Table The Pending Must Be Shown 0

Jan 21, 2009

i have the following kind of table

Raw Material ReportDateDetailOrder Given Received Opening Balance25-Oct-07order to Johson50029-Oct-07Receive from jonson10014-Jan-08Receive from jonson 5405Pending sum500505-5

as you can see that the pending order (receivable from johson) is -5 as we reveive more then order given.
i want here that when we receive more than order as in this table the pending must be shown 0.
i can do it by apply the condition when pending is

View 9 Replies View Related

Convert Detailed Table Into Summary Table

Nov 2, 2009

I have received a database containing listings of products and their sales, by month (see left side of attached file). What I actually need is to have this table converted (transposed) into a “flat database” which I can later manipulate with a pivot table (see desired output on the right side of the attached file). In the attached file I have drafted the desired output format of 1 line from the raw table, which I would like to get.

What I am looking for (if I might be so bold and ask) is a macro that will allow me to have a simmilar result for ALL the lines (there can quite a few). I think this output format can be achieved with a few simple loops, probably nested, but unfortunately I was not able to find the right sequence (being a novice in VBA).
I have tried to find the answer here, between all the posts regarding “transpose” but couldn’t find something that looked (to me) as containing a relevant solution.

View 2 Replies View Related

How To Convert Data Into A Table

Mar 4, 2013

I am doing a literature review and at the moment I have a table where I am listing all articles and ticking of their "theme", "theories used" and "methodology" like this:


Now I need to map the theories onto the articles, like this:


How do I accomplish this? (in an automated manner, obviously)

I hope the pictures are sufficiently self-explanatory, it is kind of hard to explain what I am trying to achieve otherwise. Note that ticking boxes is accomplished by changing the font to wingdings 2 and using uppercase P for checking a box. I know there are real checkboxes for this purpose, but I am generating the table from imported data and can't find another way to program excel to tick boxes if a condition evaluates to true.

Go to this link to download the original file: [URL] ....

View 2 Replies View Related

Rearranging All Data To Three Columns

Sep 19, 2006

I have a worksheet that contains data in the first three columns and then the 4th column is empty and then there's data in the next three columns and then an empty column etc. How can I cut the data from columns E to G and I to K and M to O etc....and paste it directly below the data in columns A to C? I dont know how many columns of data there are in the worksheet and every set of 3 columns of data (eg. E to G) varies in size. I recorded the following macro of what I want to do.

Sub rearrange()
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
End Sub

View 2 Replies View Related

Convert Table Data To Another Sheet?

Aug 21, 2014

I have a table in Sheet1 and it's about of student's name, lessons and class.

I want to create a macro which convert to all data like Sheet2.

View 2 Replies View Related

Fit And Convert Data To Table Template

Dec 22, 2009

Need to fit my raw data (Raw Data Sheet) to fit my table template.

Need to convert my daily data to weekly to fit to my table template.

Please see attached file for reference.

View 6 Replies View Related

How To Convert Data On Multiple Sheets Into Excel Table Format

Jan 12, 2014

I have a workbook that contains something like 50 worksheets and they all contain data with the same columns, for example column A is Project, column B is Project Name, etc. I need to convert data in each worksheet into an Excel Table. There has to be an easier way than manually converting each worksheet into a Table. However, when I group all of the sheets, the option to Insert a Table is not available even though the data starts in the same row and contains the same number of active columns in each worksheet. Is there an easier waty to insert a table in all of the worksheet simultaneously?

View 2 Replies View Related

Convert Data From Table Format Into One Long Row Of Data?

Jun 17, 2013

I have a sheet with data 7 columns wide and up to 1500 rows long. Below are a few lines from the sheet. The data from the sheet is grouped into "months" as shown in the header down the first column, "Show June, 2013..." and "Show July, 2013...", and so on. Is there a way I can make Excel re-align the data into ONE long row of data for each month? Each new month row must be "triggered" by the text (like the word "Show") because the actual cell number of where the new month begins may change with new updates. I want to do this because I plan to refresh the data once at the end of each day from the web and apply the new data to the "next" row of data. So eventually, I can have a history of "Last" values I can chart for each "strike" for each month.


Show June, 2013 Options Hide June, 2013 Options



[Code] ........

View 9 Replies View Related

Rearrange Table - Transpose Rows To Columns

Nov 2, 2007

I have problems to write a macro to rearrange the table from attachment file sheet1 to sheet2. I have many files that need to transpose rows to columns and with continually years, month, day, and the rest parameters. some to the files many content more then 20 years data.

View 5 Replies View Related

Pivot Table Columns/rows And Subtotals

Oct 13, 2009

attached Pivot Table. There is a section, which I've marked, that shows how I want my Pivot Table report to look. I've had Excel 2007 kick me out a number of times when I've tried to filter and shift rows and columns to accomplish this and areas are frequently grayed out. Can someone please assist?

I'm attaching a file rather than going into a lot of detail here because I think a visual is easier here. The raw data, my attempt at the Pivot Table and my desired outcome all show on the same tab.

View 5 Replies View Related

Convert Excel Table To Range Without Losing Data Connection To Access?

Sep 20, 2013

How to convert "Excel table" to a range without loosing data connection to access?

View 2 Replies View Related

Reference A Value In Table Using Dropdown Lists For Both Columns And Rows?

Oct 8, 2013

I want to be able to reference cells in a table by using two drop down lists. One drop down for the columns in the table and another drop down for the rows in the table. The values in the columns will be different metal materials (aluminum, copper, stainless steel, etc.). The values in the rows will be thicknesses of those materials (1/16", 1/8", 1/4",3/16",3/8",1/2",3/4",1"). I already created a drop down list for the materials and another for the thicknesses. But now I want to be able to select a particular material and then a particular thickness and receive back in another cell a value that's cross referenced in a table. Specifically, the values in the table will be the weight of the particular material and thickness per square foot. I'll populate all of the cells in the reference table manually. I'll hide the table on a separate worksheet. i haven't created the table yet.

View 2 Replies View Related

Transform 1000 Rows X 50 Columns Table Into 2columns

Mar 16, 2009

Am working with a spreadsheet of about 1000 rows and 50 columns which I need to transform into 2 columns and as many rows as needed. I have attached a simplified example of a 3x3 spreadsheet so you can see what I mean. I have already managed to get my result using Filtering and some copy and paste but I am struggling to now fully automate the solution.

View 4 Replies View Related

Convert Columns To Rows With DATA

Aug 5, 2008

i have a problem in copying many columns to to rows that originally contains data, this is gonna be really complicated so here is wt i want, let's assume i have this table .....

View 6 Replies View Related

Convert Several Rows Of Data To 2 Columns?

Mar 5, 2013

I am trying to convert some data...

I have a spreadsheet of 1000 rows on my spreadsheet and I am trying to convert them to 2 columns. Below is an example

Antidiarrheal AgentsBrand or SeriesApplicationGeneric Drug NameStrengthTypeDosage FormContainer TypeVolume
Antidyskinetics Brand or SeriesApplicationGeneric Drug NameStrengthTypeDosage FormContainer TypeVolume

I want it to look like this:

Antidiarrheal Agents Brand or Series
Antidiarrheal Agents Application
Antidiarrheal Agents Generic Drug Name
Antidiarrheal Agents Strength
Antidiarrheal Agents Type
Antidiarrheal Agents Dosage Form
Antidiarrheal Agents Container Type
Antidiarrheal Agents Volume
Antidyskinetics Brand or Series
Antidyskinetics Application
Antidyskinetics Generic Drug Name
Antidyskinetics Strength
Antidyskinetics Type
Antidyskinetics Dosage Form
Antidyskinetics Container Type
Antidyskinetics Volume

Is there a formula or a suggested way of doing a macro to acheive this?

View 1 Replies View Related

Convert Data From Columns To Rows

Dec 2, 2009

I have a spreadsheet that consists of columns of data and I need to covert it into rows depending on how the data is set up. In column A, there is a list of invoice numbers. Columns B, C, & D are as follows:

Column B: Sales Rep Name
Column C: Sales Rep Number
Column D: Sales Rep % Split

I want to have a single row for each unique invoice number. As an example, I have the following:

Row 1: invoice ABCD-1234 Sales Rep 1: 55%
Row 2: invoice ABCD-1234 Sales Rep 2: 25%
Row 3: invoice ABCD-1234 Sales Rep 3: 20%

I want the output as follows:

Row1: invoice ABCD-1234 Sales Rep 1: 55% - Sales Rep 2: 25% - Sales Rep 3: 20%

I have attached a sample workbook with two sheets; one containing the raw data and the second, which shows the desired output.

View 2 Replies View Related

Convert Data From Rows To Columns

Jan 5, 2008

I have data which is in rows. I want them to be in 5 columns.

I am attaching the sample.

for some of the entries, there will be 6 rows:
Company Name - 2nd row
Address - 3 row.
In this case, I want to combile them as one entry Separated by a comma
& placed in one column
Eg: For 3rd one in sample,

Haifa Marble & Tile
69 Garfield St
Wanted Result: Haifa Marble & Tile, 69 Garfield St

If this is not possible, then suggest how to combine two columns to make it
into one column separated by comma.

View 9 Replies View Related

Pivot Table To List Multiple Source Columns In Rows?

Jul 4, 2014

I have a huge data set with survey data (sample attached: sample.xlsx). Column headings: survey questions Row headings: respondent identifiers (names) Row data: answers to questions (numeric values, only five answer options 1,2,3,4 or 5, no blanks)

I need to build that kind of pivot table:

Rows - list of particular questions (i.e. questions no. 3, 7 and 12) Columns - list of all diffrent answer entries (eventually, it will be 1,2,3,4 and 5) - it could also be questions in columns and answers in row (no difference) Values - count answer entries (i.e. how many answers "5" are on quesiton 3)

UPDATE: explanation added.

The reason I need pivot tables: i'll have to cross analyze multiple sets of questions. I'll have to do such cross-analysis 100+ times, so, writing a formula for each time does not quite work...

View 6 Replies View Related

Excel 2010 :: Pivot Table With Fixed Rows And Columns?

Jun 21, 2014

I'm trying to produce a fixed format pivot table in Excel 2010. Normally I'd just construct a manual table using COUNTIFS, SUMIFS etc, howver, for this exercise the requirement is to be able to click on any field and have a tab pop up with the relavent data a la Pivot Table. But....I need the Pivot table to have a fixed format (which I can do with 'Preserve cell formatting on update') and to have all of the rows and columns in irrespective of whether there is data or not (i.e. if I haven't sold any apples in June, I still want the 'Apples' field to appear, just with a value of zero). I had assumed that the option I needed was 'Show items with no data on rows/columns' but these options are greyed out. I've tried right-clicking on the whole table, on individual fields and on labels but still get the same greyed out options.

Is there any way I can retain all of the rows and columns?

View 1 Replies View Related

How To Convert Data Columns To Rows In Excel

Dec 12, 2013

Solution to convert data columns to rows in excel.

View 4 Replies View Related

Copyrights 2005-15, All rights reserved