Convert Raw Data Matrix To Multiple Column Data?

Sep 6, 2012

Here's a sample of the document I have, the original has several thousand entries, so figured for the sample I'd limit it. The raw data is on sheet 1, and sheet 2 is what I want to have to more easily manipulate the data. I started doing it manually, but I'm sure there's a far better method that I just don't know about yet. sample.xlsx

For those that don't want to download the document:

My data is like this:

ID1 Field1
ID1 Field2
ID1 Field3
ID2 Field1
ID2 Field2
ID2 Field3
ID3 Field1
etc.

What I want is this:

ID1 ID2
Field1 Field1
Field2 Field2
Field3 Field3
etc.

View 2 Replies

Convert Columnar Form Of Data To Matrix

Aug 6, 2012

I am looking to convert data stored in columnar form to a matrix.

I found this link: [URL] ........

Which does the reverse of what I am trying to accomplish. Would it be eaiser to modify the above or start over?

View 3 Replies View Related

Excel 2010 :: Convert Matrix Data To A List?

Oct 16, 2013

I have been using Excel to record the routine daily issue of items to different groups in a matrix layout, I use a different workbook for each month with worksheets for each group. The matrix takes the form of the item issued being the left hand column and the date issued the top row of the matrix, the quantity issued is recorded at the intersection. Each item can have a different quantity issued on different days. I'm using Excel 2011 for Mac but could use PC Excel 2010. Is there a way to convert the data held in this way to a list? What I'd like to achieve is a list showing the Item, the Quantities Issued and the the Issue dates

View 9 Replies View Related

Convert Column B As Multiple Column Titles And Move Data In Column C Into New Columns?

Jun 30, 2014

View 8 Replies View Related

Convert Column Of Mixed Data Into Unique Data

Jan 31, 2007

I need to convert a column of data which will have repeated values into a column that turns that data into data with all unique values. Below is an example.

Column1Column2
AppleApple_1
OrangeOrange_1
PearPear_1
AppleApple_2
PearPear_2
PearPear_3
AppleApple_3
OrangeOrange_2
OrangeOrange_3

I had a solution until I wanted to turn this range into a list. The solution was.

=If(ISBLANK(A2),"",A2&"_"& COUNTIF($A$2:A2,A2))

When this data is in the list the <A2> in the range stops incrimenting from row to row and simply turns into the last row of the list. I have an example spread sheet upon request.

View 9 Replies View Related

Convert One Column Of Data To 257 Columns Of Data

Mar 11, 2008

I have exported a text file from a universe database and need to import it into excel.

Below is an eg of one section. The text file contains approx 257 of these, all one below the other.

Between each set of dotted lines is one user profile (not data all shown in eg) .

Each one (user) is NOT the same amount of lines(rows) deep and only the dotted lines separate each user.

A simple import places all data into one very long column.

I need a way to place each user in there own column.

I have user names as column headings the labels of the values below as row headings. I cannot change this.

Manually entering the data took a very long time and i need to update this workbook regularly.

In the text file, " the variable......: value " (eg UserCode.....: XYZ) sets are all lined up so that using a text editor, with column mode, I can delete all the data headings/labels up to the space after the colon.
This leaves me with 7000 rows of values (eg XYZ) one column wide and various numbers of rows deep per user.

Is there a way to import the data and seperate each user via the dotted lines or other so that each users data is contained in consecutive columns. 1 user profile per column.

The standard import utility allows for column placement (delimted, fixed width). Can this be done on rows, sort of flip it 90 degrees and use the dotted lines as column markers. Just guessing now. Any and all assistance would be most appreciated.

Another issue (not as important but still helpful) is that the "procudures barred" section of user profile can also be one row(line) or serveral. Problem is each Name...:Value set must be on one row(line) for everything to match in the work book.

Using custom macros in my text editor i moved all the 'proc. barred' data to a single row. Alot of manual editting was involved and any ideas on this area would also be muchly appreciated.

One EG user profile (not shown, but all the colons line up) ....

View 9 Replies View Related

Making Report And Show Data From Matrix With Header And Data?

Jul 4, 2014

I added an attachment with an example. You will see a matrix, with in the most left column an ID, then the headers of the matrix has different numbers that mean something.

What I want to do is, you fill in a number in the combobox, then press on a button and a report will be made on a new worksheet. With a list of the numbers and dates in the matrix that the ID has. Like shown in the example sheet in the workbook.

View 1 Replies View Related

Convert Column Data To Row Data?

May 22, 2014

I need to convert an Excel column sheet into rows for loading into a database, lots of columns, need to do repetitively over the enxt few months: For example...

Cust SKU Jan Feb Mar Apr May...
AAA 1234 10 19 14 ... ...
BBB 1234 21 88 42
BBB 5678 56 56 43

...becomes
Cust SKU Month QTY
AAA 1234 Jan 10
AAA 1234 Feb 19
AAA 1234 Mar 14
BBB 1234 Jan 21
BBB 1234 Feb 88

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

A B C D E F
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

Outer
Inner
Tip
Nose
Outer
Zip
Stiffner
Nose
Inner
Tip
Zip
Stiffner

View 9 Replies View Related

Convert Column Data Fields To Row Data Fields In Pivot Table

Feb 8, 2014

CountryHourDataTotalData
Austria - A10Sum of SeiA51CountryHourSum of SeiASum of SeiT
Sum of SeiT4.88Austria - A10514.88
1Sum of SeiA561562.83

[Code]....

left side pivot created in vb 6.0 & right side pivot table created manually in excel.

i want to generated pivot table using vb 6.0 same as right side pivot.

Set PRange = ws1.Range("R1:Y" & finalrow)
Set PTCache = wb.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=PRange)
Set PT = PTCache.CreatePivotTable(TableDestination:=ws2.Cells(1, 1),

[Code]....

View 2 Replies View Related

Convert Column Data To Rows?

Apr 20, 2014

I student data in the following format

STUD ID, Surname, Forename, Subject
E.g
0001,Smith, Stan,Biology
0001,Smith,Stan,Chemistry
0001,Smith,Stan,Physics

Etc

I would like to display it so instead of there being multiple rows per student there is just 1 row with each subject in a new column

e.g

STUD ID,Surname,Forename, Subject 1, Subject 2, Subject 3

e.g

0001,Smith,Stan, Biology, Chemistry, Physics

Is there any way I can do this quickly for hundreds of students?

View 12 Replies View Related

Convert The Data In Column B To Number?

Jul 30, 2014

convert the data in column B to Number so that i can perform action in Column D. see attachment for the query.

View 3 Replies View Related

Macro To Transpose Multiple Data In Cell Separated By Commas To Each Data In Column

Jul 15, 2014

I have a table in the format below with about 3500 rows

Column A
Column B

0001
All vehicles, Retirements

0002
All vehicles, Retirements, Addition

0003
All vehicles, Retirements, Addition, Deletion from Y

I would like to change it to the following format:

Column A
Column B

0001
All vehicles

0001
Retirements

0002
All vehicles

0002
Retirements

0002
Addition

0003
All vehicles

0003
Retirements

0003
Addition

0003
Deletion from Y

View 3 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 From Text To Column And Paste The Converted Data In Other Sheets?

Apr 18, 2014

I want some vbs code on my 'Copy All' button in Email Data sheet which will do my work. Its a bit lengthy but who knows the concept will find it easy.

I have one sheets -

Sheet attached - Worksheet .xlsm

Workshet.xlsm [Data in 'Mod' sheet i)should convert from text to column and paste in 'Row Data' sheet ii) then transfer form Row Data sheet to 'Email Data' sheet 'Mobile Data' sheet and 'Other Data' sheet when Copy All button is clicked in Email Data sheet.

There should be some logic which will fetch appropriate date from 'Mod' sheet to the respective sheet . We can have some kind of logic like

If 'Register' found then take the value of register and workstation and paste in Email Data sheet.

All data below should be fetched from Row Data sheet which is blank now and will get the data when we click the Copy All button ,

1st Register Count:

Below Register Count (*) i.e. 16 If workstation count present copy to E15 in Email Data sheet in Table B

Below Register Count (*) i.e. 9 If register count present copy to E16 in Email Data sheet Table B

2nd Register Count:

Below Register Count (*) i.e. 8942 If workstation count present copy to C15 in Email Data sheet in Table B
Below Register Count (*) i.e. 2883 If register count present copy to C16 in Email Data sheet in Table B

3rd Register Count:

Below Register Count (*) i.e. 0 If workstation count present copy to E18 in Email Data sheet in Table B

Below Register Count (*) i.e. 1 If register count present copy to E19 in Email Data sheet in Table B

4th Register Count:
Below Register Count (*) i.e. 98 If workstation count present copy to C18 in Email Data sheet in Table B
Below Register Count (*) i.e. 999 If register count present copy to C19 in Email Data sheet in Table B

Substr:
B32:B44 will be pasted in C29:C41 in Email Data sheet in Table C
i.e. 10 to 22 values should match in corresponding 10 to 22 values in Email Data sheet , if not not any value then 0

B50:B62 will be pasted in B29:B41 in Email Data sheet in Table C
i.e. 10 to 22 values should match in corresponding 10 to 22 values in Email Data sheet , if not not any value then 0

View 1 Replies View Related

Convert Horizontal Data Into Cumulative (one-column) Vertical List

Feb 15, 2012

I have data (400 rows) that looks like this: (23 columns in total): (for this example I am using only 4 rows and 7 columns = 28 rows)

IN_0168 IN_0164 MH_0007 IN_0172 WCD3_03 WCD3_04 #N/A
IN_0176 IN_0172 WCD3_03 WCD3_04 #N/A #N/A #N/A
IN_0185 MH_0006 IN_0164 MH_0007 IN_0172 WCD3_03 WCD3_04
IN_0191 IN_0193 IN_0195 IN_0199 WAT10 #N/A #N/A

and I want it to look like this:

IN_0168
IN_0164
MH_0007
IN_0172
WCD3_03
WCD3_04
#N/A
IN_0176
IN_0172
WCD3_03

[code]....

This is what I was trying to do:

For m = 2 To 5
For n = 1 To 23
For b = 2 To 100
Sheets("Hierarchy").Select
Cells(m, n).Select
Selection.Copy
Sheets("HierN_H2V").Select
Cells(b, 1).Select

[code]....

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

Convert Matrix To Table

Jun 4, 2014

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

Input file:
Capture1.PNG

Output :
Capture2.PNG

View 4 Replies View Related

Convert List To Matrix

Jun 24, 2007

I want to take information from a list and put into a matrix using VBA. So my problem is to find the correct cell in the matrix.

View 5 Replies View Related

Convert List (vector) To Matrix

Dec 18, 2007

I'm looking for a way to automatically convert a list of values into a matrix.

For example,

A1A11000
A1A2998
A1A3468
A1A4491
A2A1998

[Code] .......

should be converted to

A1A2A3A4
A11000998468491
A29981000464488
A34684641000992
A44914889921000

In this case it could still easily be done manually, but if the matrix has more dimensions it will get harder.

View 5 Replies View Related

Extract Data From Matrix

Jun 22, 2008

I'm running a small messenger company and I've got a geographic area divided into five zones: 1,2,3E,3W,& 4

Deliveries within 1 zone will be charged for one zone.
Deliveries from Zone 1 to Zone 2 will be charged for 2 zones. Etc.

I've got a list of zones in my From column and also in my To column.

I need to write a formula that will give my customers the Zones Charged in a seperate cell.

I learned how to do something like this back in school, but don't even know what to call it anymore. Maybe it's some kind of comples IF function.

FREE Deliveries to anyone in Long Beach, CA to the winner!!!

View 12 Replies View Related

Finding Data From A Matrix

Feb 17, 2009

On my example sheet it shows how many cables and how the cable length per job. What i need is for the hour/job column to be populated from the Hours sheet by matching the column and row. e.g 4 cables at 40 metres would give me cell D7 (27.89), as 25 metres would be no good - so rounding up would be required.

View 2 Replies View Related

Turning Data Into Matrix?

Oct 18, 2011

I have 2 lines with pairs and 3rd with corresponding values, nee to construct a matrix out of it with formulas

Example
Need to turn

AAABBDBCDCDC0.30.20.130.550.840.43

into

ABCDA11-0.31-0.21-0.13B0.311-0.551-0.84C0.20.5510.43D0.130.841-0.431

So the one that stands above in the initial file goes to the top line in the matrix. 2 line -> column in matrix. In case there is no match need to seek reverse in 1,2 and reflect as 1-VALUE in the matrix

View 3 Replies View Related

Plot Data To Matrix

Aug 17, 2008

I have an Excel file with a sheet that has the following columns: Name, Activity, Start Date,
End Date. What I would like to do: I would like to create summary sheet with a calendar-like
matrix, with the names as rows, and dates as columns. I would then like to plot the activity in
the corresponding dates (based on the start and end date). Note: If there are schedule
conflicts, the conflict should show up in another row

- Is this even possible to do in Excel?

Source:
Name Activity StartDate EndDate
PersonA Vacation Jan1 Jan3
PersonA Office Jan4 Jan5
PersonA Field Jan6 Jan7
PersonB Vacation Jan1 Jan3
PersonB Field Jan4 Jan7
PersonB Office Jan5 Jan5
Summary:
Name Jan1 Jan2 Jan3 Jan4 Jan5 Jan6 Jan7
PersonA Vacation Vacation Vacation Office Office Field Field
PersonB Vacation Vacation Vacation Field Field Field Field
PersonB Office

View 9 Replies View Related

Pulling Data From Cells Into A Matrix?

Jun 22, 2013

In VBA I'm trying to grab numbers from a cell range (ie: A1:A10), and save them to a matrix or variable. I will later need to grab additional numbers and add it to that matrix. I have experimented several different ways, but belows demonstrates what I'm trying to do:

VB:
my_data = Range("A1:A10").Value
my_data = my_data&Range("B1:B10").Value

View 2 Replies View Related

Create Matrix Of Data Automatically

Jan 30, 2013

I have 2 types of data: Product & Store

And the data would go like this:

A
Corn Flakes
Frosted Flakes
Froot Loops

B
Store 1
Store 2
Store 3
Store 4

The number of entries of course is a lot bigger, that's why I'm trying to make it auto.

So what I need is to have a table created that would mix both data type in 2 columns, like this:

Product
Store

Corn Flakes
Store 1

[Code]...

How would this be done automatically by a formula?

View 9 Replies View Related

Formula For Pulling Data From A Matrix

Nov 21, 2006

Im currently working to speed up my monthly reporting process. Each month I pull data from the ledger by account & office. Below is an example of what the format of the ledger looks like.

My next step is to report the amounts by account number for each office. At the moment I do this manually, which takes a very long time. My goal is to be able to paste the ledger data on a worksheet and have my report populate automatically.

In regards to the account numbers from the ledger, we summarize the accounts using the first 3 digits of the account. For example, account number "1113454" would be considered a "111" account. Account number 3335454 would be considered a "333" account.

************************************************************************>Microsoft Excel - 3Q Global Variance Analysis.xls___Running: xl2000 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutB16C16D16E16F16B22E22B23E23B24E24B25E25B26E26=
ABCDEF1Ledger2******3*Office*1Office*2Office*3Office*4Office*54*SEP-06SEP-06SEP-06SEP-06SEP-065Account*Number*****6111234127.00766.004,446.007,865.005,509.00711135433,000.0034,566.0099,433.00100.00471,612.76811167645,677.009,898.002,212.0034.00877.00911190932,445.00466.0043,435.00343.005,665.00101118881,876.00433.006,566.00900.0020,398.4411113454121.0032.00900.0075,329.3222.001211345517,678.005,443.00434.00233.0064,556.00133331115,658.00875.00222.0032.00654.001433350090,932.007,634.00122,246.002,134.0055.0015333009123.00543.00990.009,005.00888.0016Sum227,637.0060,656.00280,884.0095,975.32570,237.2017******18My*Report*19******20111*Balance*113*Balance*21OfficeBalance*OfficeBalance*22Office*1113,125.00*Office*117,799.00*23Office*246,129.00*Office*25,475.00*24Office*3156,092.00*Office*31,334.00*25Office*49,242.00*Office*475,562.32*26Office*5504,062.20*Office*564,578.00*27******Ledger*
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

View 9 Replies View Related

Matrix - Need Data Sorted Into Lists

Mar 9, 2008

I have a matrix of mixed numbers (going across many columns and rows) in Sheet1. They are a mix of positive and negative numbers, In Sheet2 I need 2 lists to be automatically displayed, taken from the data in Sheet1.

The first list (column) is for positive numbers only, the second is for negative numbers.

There is one filter: only numbers over a specified value (for the positive list) must be used from the data, and only numbers under a specified value (for the negative list) must be used from the data. The 2 filters can be stored in any cells in Sheet2 (the worksheet where the lists will be), lets say, for ease of use, A1 and B1.

But here's the tricky part: I need the location reference of each number in the matrix displayed next to the number in the list!!! Eg: in the matrix, I have names running down the left (A2 to A25) and the same names in the same order running across the top (B1 to Y1) - just like any basic matrix! If A6 is called "John" and L1 is called "Pete" and their value (at cell L6) is 10, then I need them to be displayed in the "positive list" amongst the others (sorted automatically into numerical order) with the words "John" in the cell to the right of the score, and "Pete" in the one after that to the right: so it will look like:

A2 B2 C2
10 John Pete

(remember that these 2 lists are on Sheet2, not on the matrix worsheet! A1 and B1 contain the 2 filters for the positive and negative lists).

Of course, this must only happen if the filter in A1 (the positive filter) is 10 or under. If it was anything above 10, then this one would not make the list.

As for duplicates in the matrix coming up twice in the lists, that's not a problem this wont matter! It's ok for the lists to have constant duplicates displayed eg: 10 John Pete will also have next to it 10 Pete John.

View 9 Replies View Related

Distance Matrix For Multivariate Data

Aug 7, 2006

I have a multivariate dataset with a large number of cases. eg. 50.000 cases (data points) and 40 variables. I need to calculate the distances between each pair of data points in this 40-dimensional space. Obviously i cannot produce a whole data matrix, which would have to be 50.000 x 50.000 big. but i need at least the average distance - if N is the number of cases there are (N*N-N)/2 possible pairs of points. Idealy, the (N*N-N)/2 distances could be placed in a column so i could make a histogram of the values. Still this doesn't seem like a very good solution as the (N*N-N)/2 distances if N=50.000 would fill about 75 worksheets!

Here is a simplified exaple of what i want to do, but with N=4 and only 3 variables:
original data:
IDv1v2v3
1103010
2202510
3202020
4402015
Distance matrix:
1234
10 11,1803398917,3205080832,01562119
211,18011,1803398921,21320344
317,32 11,18033989020,61552813
432,01 21,2132034420,615528130

View 2 Replies View Related

Compare Blocks Matrix Of Data

Aug 29, 2006

I have the follwing data in A1:T17

3,4,7,9,10,11,21,32,33,35,37,41,47,57,60,64,69,72,74,75
4,6,9,10,12,13,15,21,22,31,35,47,49,52,56,63,64,72,74,75
4,6,9,10,15,21,31,33,34,41,42,45,46,47,57,60,68,72,74,78
3,4,6,10,11,13,18,21,30,32,33,35,46,53,60,67,69,74,77,78
6,9,13,16,21,22,31,46,48,49,52,61,63,64,69,70,71,75,78,79
3,4,7,10,14,17,18,21,28,31,33,36,37,43,47,57,65,69,75,80
4,7,13,15,17,25,29,32,37,42,45,47,50,57,60,64,68,71,72,74
3,7,10,11,16,18,28,34,35,43,47,51,52,55,56,57,60,64,71,72
8,9,10,12,16,21,22,28,38,47,49,51,52,53,54,55,64,66,71,72
4,5,6,9,12,15,19,20,30,34,35,38,45,47,54,56,63,65,72,78
5,6,9,12,15,21,26,31,32,43,44,47,64,66,67,68,69,74,75,80
..................

I want to compare the first set of 20 numbers (A1:T1) with the second set (A2:T2) and check how many numbers match. ** If the matched numbers
are >=10 ** then list them to the right of cell W1. In this example, 10 numbers matched are 4,9,10,21,35,47,64,72,74,75. List them in W1:AF1.

Now compare (A1:T1) with (A3:T3). In this example, 11 numbers matched 4,9,10,21,33,41,47,57,6,72,74 list them in W2:AG2.

Now compare (A1:T1) with (A4:T4). 10 numbers matched 3,4,10,11,21,32,33,35,60,69 list them in W3:AF3.

Compare (A1:T1) with (A5:T5). Here only 5 numbers matched 9,21,64,69,75 (Which is ** less than 10 ** - does not match the criteria) DO NOT LIST
this.

Compare (A1:T1) with (A6:T6). 11 numbers matched, 3,4,7,10,21,33,37,47,57,69,75 list them in cell W4:AG4.

.................

Sub Compare()

Dim r As Integer, rr As Integer, c As Integer, cc As Integer, opc As
Integer, opr As Long

opr = 1
For r = 1 To 16
For rr = r + 1 To 17
opc = 23
For c = 1 To 20
For cc = 1 To 20
If Cells(r, c) = Cells(rr, cc) Then
Cells(opr, opc) = Cells(r, c)
opc = opc + 1
End If
Next cc
...............................
Next r

End Sub
The above code runs fine but If I increase my rows from 17 to 1200 it takes 3 1/2 hours to complete. Can it be reduced?

View 9 Replies View Related