Convert Two Dimentional Table To Single Dimention

Aug 8, 2007

I have a two dimentional table that I want to be able to convert to single dimention records so I can import into database as records.

Sample two dimentional table
Description, Week1, Week2, Week 3,
Widget1, 200, 100, 150
Widget2, 75, 25, 30
Widget3, 10, 20, 30

I want to be able to convert above table into format as below

Widget1, week1, 200
Widget1, week2, 100
widget1, week3, 150
widget2, week1, 75
widget2, week2, 25
widget2, week3, 30
widget3, week1, 10
widget3, week2, 20
widget3, week3, 30

I do this each week with with approx, 40,000 records, (single dimention) copying and pasting is becoming a chore. Does anyone know a method to address this via functions, macros, formulas, etc. Number of rows and columns vary each time I repeat the exercise.

View 3 Replies


[VBA] Setting 2nd Dimention Of A 2D-array

Aug 10, 2009

Im trying for quite some time now to set the array in the 2nd dimention of a 2D-array to the result of a function which returns an array of dbl but i just can't get it to work.

The result of the Interpolate-function has the same length as mintSpan.

ReDim dblProductionRate(1 To 5, 1 To mintSpan)

For intHC = 1 To 5
If CheckHC(intHC) And Owner.CheckHC(intHC) Then
dblProductionRate(intHC) = InterpolateProductionProfile(intHC, mdblReserves(intHC), GetProfiles(intHC))

View 9 Replies View Related

Convert Word Single

Feb 19, 2010

I want that I write an word and it convert in to a single cracter. I ATTACHED HERE WITH FILE SEE.

View 4 Replies View Related

Convert Multiple Rows To Single Row

Feb 4, 2014

I have a database of addresses that has each address as several rows which I want to convert into a single row. In the source file there is a blank row between records. I have attached a sample file with two sheets in it. One is the "before" data format and the other sheet is the "after" example of how I would like it to come out. We have thousands of records to convert so I need something that can be automated instead of a massive cut and paste operation.

Here is the sample: sample conversion.xlsx

View 4 Replies View Related

Convert Single Row To Multiple Rows

Mar 16, 2014

Macro to sort column data.

I have a spreadsheet in the following format (just a flavour of the format)

SD 1


Each row refers to a survey response, the first ten columns being standard demographics.

Following this each group of 5 columns for the next 100 columns represents a single response. What I want to do is convert each single row to multiple rows with the same first 10 columns and each single response on its own row like so:



I found an excellent similar example on this board here Convert a single row to multiple rows with the same A column value However I would like my macro to convert to rows even if the cells are blank So each existing row will generate 10 individual reponse rows regardless of data found).

I am interested to know how this would work and how i could modify the variables to acommodate 15 standard demographic fields and 20 columns per individual responses.

View 5 Replies View Related

Convert Numbers To Single Letters

Jun 8, 2007

In row G, I have a wide range of values from -3000 up to 2500.

These values are generated from a formula. =A5-B5.

I need a script that will scan row G between G5:G500 and give them a letter value.

Convert anything less than 0 to an A
Convert anything equal to 0 or equal to 1 to a B
Convert anything between 2 and 7 to a C
Convert anything greater than 7 to a D

It can place the new categorized letter value over the original formula in row G or put the letter value right next to it in H.

View 9 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

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 View Related

Convert Repeated Entries In Column To Single Row

Jul 14, 2013

I have data like this

bobkiwi B
bobpear C


Not sure how to do it

View 3 Replies View Related

Convert Single Column To Multiple Columns

Dec 6, 2007

How can I sort a single column of data to multiple columns of data. e.g. I want to convert ~1800 rows of data from 1 column to 3 or 4 columns.

View 14 Replies View Related

Automatically Convert Date Into Single Digit?

Jan 3, 2012

I'm looking for a formula that may automatically convert date into single digit e.g today is Jan 03, 2012 & after adding (1+0+3+2+0+1+2) it will come out 9 ....

View 6 Replies View Related

How To Convert Multiple Coloums Data Into One Single Row

Sep 23, 2009

I have a data in different coloumns, that I want it to in one single Coloumn but different rows.
The data is like below

1 Outer Inner Tip Nose
2 Outer Zip Stiffner Nose
3 Inner Tip
4 Zip Stiffner

The data are in A to F coloumns & in Different rows. This I want in one row - Like


View 9 Replies View Related

Convert Multiple Columns To Single Rows

Feb 27, 2007

I have a very large sheet of all US zip codes by county name. Unfortunately, the document builder decided to list up to EIGHTEEN columns of zip codes per county name... I assume to make it easier to look at.

I now need to rebuild the sheet to have one column of county names and *one* column of zip codes, which will be a nearly impossible task if I don't find a way to automate the conversion.

Attached is a sample... sheet 1 is my initial state (except here its 5 columns rather than 18), and sheet 2 is my hoped for end state. Notice that the zip codes can, but don't always, fill every column allotted.

View 3 Replies View Related

How To Convert Worksheet To Single Page 11x14 PDF Document

Dec 12, 2012

I have a workbook with four single page 11x14 worksheets. I'd like to convert each worksheet to a single page 11x14 PDF document so that I can take it to my local printer and have it printed on cardstock. I've tried two different free conversion programs and am frustrated to the bone. The pages keep running over at the bottom onto a second page, and how to make it fit-to-page.

I'd like margins of about 1/4" on the left, right, and top. No headers or anything like that. Then fit to page on 11x14 paper size. I think that's it.

View 4 Replies View Related

Convert Byte Array Containing Floating Point To Single

Sep 6, 2007

I have a byte array that contains a 4 byte floating point number. How would I convert this byte array to the single typed floating point number it contains?

View 9 Replies View Related

Convert Multiple Versions Of Cell Contents To Single Version?

Feb 28, 2014

I would like to automate the conversion of multiple versions of company names contained in cells to a single company name. i.e. XYZ Inc., XYZ Co., XYZ to all cells XYZ. My data file contains numerous versions of company names. I want them to all be the same company name so sorting data is easier. I now do this manually and it is getting tedious and time consuming. Can I build a table with the different versions that then chamges the cells to a single version that I choose?

View 1 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

Convert Hourly Time Series Data From Rows To Single Column

Jan 8, 2014

I have A:1 - X365 filled with hourly energy prices for 2013. I need to convert these to a single column - A1:A8760. I have tried writing some code and some macros with no success.

If I were to do it manually 365 times, the macro would look like this:

Copy Row A1:X1
select sheet 2
Paste Special > Transpose (fills A1:A24)

repeat for rows 2-365

Tried using this from a previous post, but it takes A1:A24 then B1:B24 and so on and makes them a single column - so it doesn't work unfortunately.

Sub MakeColumn()
Dim rng As Range
Dim LastRowSrc As Long
Dim LastRowDst As Long

Set rng = Worksheets("Sheet1").Range("A1")

[Code] .....

View 3 Replies View Related

Query From 2 DSN's Into A Single Table

May 26, 2009

Our company's accounting system is physically split into 2 company databases, CompanyA and CompanyB. I'm trying to write a query which will pull the customer number and the accounts receivable balances from both companies into a single Excel table, for any customer with a total balance 0.

I have created the following query, which doesn't generate any errors, but is only successfully importing the data from the first company.

The CompanyA DSN is specified in the query ...

View 9 Replies View Related

Extract Top 10 Customers From Single Pivot Table?

Jun 13, 2013

I have come across a situation where I am listing the top 10 customers based on their sales. I have a worksheet for each product I sell. I'm wondering if there is a way to write an excel formula to pull just the top 10 customers from just a single pivot table (respective to the product worksheet) instead of having to create a new pivot for every product and sort based on top 10 and then link to my spreadsheets?

View 2 Replies View Related

Creating Bill Of Material From Single Table

May 14, 2014

I would like to create Bill of material from single table. I need to select multiple parameters, to expend it till the lowest level, so I will try to explain:

I have one table (ODBC) and there's all data we need. Parent number col A, Item number col B and quantity col C. First level I select main Item number A1234 (this is only thing that I should choose, everything else should be automatically), I get table with all items that are parent A1234 (let's say 10 items). Now I need to look again one level lower in same table for items that have Parent item in list of those 10 items listed earlier (let's say 30 items) and multiply their quantities with quantity of their Parent Item (total qty could be in column D). Then one level lower for items with parent items in those 30 and so on and so on. So when I choose main Item I would like to get table like below (take notice that real table has over a 100.000 items, but I want to show only Bill of material for the main item till the lowest level).

Parent Item
Total QTY


[URL] ...

I'm flexible and ok to use VBA, SQL, Excel functions, multiple tables (how to select multiple parameters??)

View 3 Replies View Related

Table Of Calculations Down To A Single Array Formula

Feb 9, 2010

I'm trying to condense a table of calculations down to a single array formula, but am getting stuck on one piece of it.

The table data is very simple, and can be in just two columns:

1, value
2, value
3, value
4, value
5, value

Where 1 thru 5 are time periods, and values are various numbers.

I want to perform the GammaDist function on each value. It requires a time period input, and in this case it is the 1 thru 5 in the table. So at the end of period 5, the formula for the first value would be

=Gammadist(5,x,y,TRUE) (x,y values not important here)

and the formula for the second value would be:


The only thing that changes is the period number.

So, my goal is to write an array formula that will sum the GammaDist for each of the 5 rows, for all timeperiods (which is 5 in this case)

I can get this far:


But this passes 1 thru 5 to all rows, I only want 1 thru 5 passed to row 1, 1 to 4 passed to row 2, etc.

View 9 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

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

Convert A PDF Table Into Excel?

Dec 5, 2013

How do i consistently convert a PDF table to Excel? When in the PDF i am recognizing the text and then trying all available ways to either copy or export the selection (to all available file types!) but it only works in a usable manner about 10% of the time....

View 5 Replies View Related

Convert Matrix To Table

Jun 4, 2014

I need to convert a Matrix to a Table. Something like this:

Input file:

Output :

View 4 Replies View Related

Convert List To Table?

Jun 14, 2012

I have three columns of data starting in row 5, the headers of these columns show Origin, Destination and mileage. Is it possible to have a macro that converts the data into a table?

View 2 Replies View Related

Convert Table Into A List

Jun 3, 2014

I am trying to convert a table into a list, please see the example, I want to convert this table





[Code] ........

View 1 Replies View Related

Convert From Table To A List

Sep 18, 2008

I need to convert this kind of table to a list (like in below example)
Can someone give me an instruction or macro to do this ?

A 3 5 7
B 2 4 6
C 8 9 10

Column1 Column2 Column3
A X 3
A Y 5
A Z 7
B X 2
B Y 4
B Z 6
C X 8
C Y 9
C Z 10

View 9 Replies View Related

Convert Layout Of Table

Jul 13, 2007

In the attached file you'll find an original table and the requested Table in its new layout. I thought to use TRANSPOSE in an Array- Function but it didn't work as expected. However, I managed to present the DATA (light green range) with the help of SumProduct. My Question goes like this: Is there a way to present the Upper and Left Headers (in red font) with Formulas ?! (I typed them by hand).

View 2 Replies View Related

Copyrights 2005-15, All rights reserved