X-Y gridded data into columns

Hello. I have datasets exported from map data as large grids
(1024x768). I want to transform them into 3 long (>768000 rows)
columns, with X, Y, and the value from the grid. Example:

B A A
C D A
C C D

to be put into:

X Y value
1 1 B
2 1 A
3 1 A
1 2 C
2 2 D
3 2 A
1 3 C
2 3 C
3 3 D

Can anyone help?

0
CM
11/15/2009 7:22:52 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
725 Views

Similar Articles

[PageSpeed] 28

On Sun, 15 Nov 2009 11:22:52 -0800 (PST), CM <schrader23@gmail.com>
wrote:

>Hello. I have datasets exported from map data as large grids
>(1024x768). I want to transform them into 3 long (>768000 rows)
>columns, with X, Y, and the value from the grid. Example:
>
>B A A
>C D A
>C C D
>
>to be put into:
>
>X Y value
>1 1 B
>2 1 A
>3 1 A
>1 2 C
>2 2 D
>3 2 A
>1 3 C
>2 3 C
>3 3 D
>
>Can anyone help?


In cell A2 put the following formula:
=INT((ROW()+x-2)/x)    (where x is the number of columns in your grid)

In cell B2 put the following formula:
=MOD(ROW()-2,x)+1  (where x is the number of columns in your grid)

In cell C2 put the following formula:
=INDEX(mygrid,A2,B2)  (where mygrid is the range for your grid)

Copy cells A2:C2 down to row x*y+1   (where x and y are the number of
columns and rows respectively in your grid)

Hope this helps / Lars-�ke
0
Lars
11/15/2009 8:21:15 PM
On Nov 15, 2:21=A0pm, Lars-=C5ke Aspelin <lar...@REMOOOVE.telia.com>
wrote:
> On Sun, 15 Nov 2009 11:22:52 -0800 (PST), CM <schrade...@gmail.com>
> wrote:
>
>
>
>
>
> >Hello. I have datasets exported from map data as large grids
> >(1024x768). I want to transform them into 3 long (>768000 rows)
> >columns, with X, Y, and the value from the grid. Example:
>
> >B A A
> >C D A
> >C C D
>
> >to be put into:
>
> >X Y value
> >1 1 B
> >2 1 A
> >3 1 A
> >1 2 C
> >2 2 D
> >3 2 A
> >1 3 C
> >2 3 C
> >3 3 D
>
> >Can anyone help?
>
> In cell A2 put the following formula:
> =3DINT((ROW()+x-2)/x) =A0 =A0(where x is the number of columns in your gr=
id)
>
> In cell B2 put the following formula:
> =3DMOD(ROW()-2,x)+1 =A0(where x is the number of columns in your grid)
>
> In cell C2 put the following formula:
> =3DINDEX(mygrid,A2,B2) =A0(where mygrid is the range for your grid)
>
> Copy cells A2:C2 down to row x*y+1 =A0 (where x and y are the number of
> columns and rows respectively in your grid)
>
> Hope this helps / Lars-=C5ke

Thank you very much. This works well.
cheers,
CM
0
CM
11/21/2009 9:16:41 PM
Reply:

Similar Artilces:

"Sending messages x of x" in Outlook 2007 status bar
Hello, When I send a message in Outlook 2007, appears "sending message x of x" where x is greater than 1. I read that it seems automatic responses, but automatic responses are deactivated. Any idea? Thanks for your time, Thunder It's a bug in outlook- x-1 should = the number of email accts in your profile. -- Diane Poremsky [MVP - Outlook] Outlook Tips: http://www.outlook-tips.net/ Outlook & Exchange Solutions Center: http://www.slipstick.com Outlook Tips by email: dailytips-subscribe-request@lists.outlooktips.net EMO - a weekly newsletter about Outlook and Ex...

Data migration into MSCRM
We are currently using Act 6 in our small office and we would like to install MSCRM. What is the best way to mirgrate all data from Act to CRM? Does MSCRM have a built in migration tool or do we need to use a 3rd party product? Thanks Tony Hi Tony, You need to use Scribe Insight. They have some pre-built Adapter for ACT! and MSCRM (in fact, you buy the product "Scribe Insight for Microsoft CRM") and have pre-built data maps for the ACT! to MSCRM migration. The great thing is that you don't need to shell out $4000 for the full product - there is a 45-day "migrate...

How to return more data than will fit on a worksheet
How to return more data than will fit on a worksheet. For example, I need to import 170000 rows from MS Access to MS Exce -- Ole ----------------------------------------------------------------------- Oleg's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=400 View this thread: http://www.excelforum.com/showthread.php?threadid=48819 Oleg wrote: > How to return more data than will fit on a worksheet. > For example, I need to import 170000 rows from MS Access to MS Excel > > 1) Import it in two chunks to two or more sheets, or 2) import it in two...

Money 2006 and size of Data File
My Money 2004 file is 32 MB. When I had problems with upgrading to M2005, support suggested that my file was too large to upgrade successfully. Has anyone been able to update to Money 2006 with a large data file? I gave up on Money 2005. I might try Money 2006 if I hear some positive results from some veteran users. Daryle That's a rediculous response from the support person. My file is roughly twice your size and upgraded correctly. Unfortunately, this leads me to believe that there is some underlying corruption in your file. It might be worthwhile running salvage on the f...

Chart Source Data
Hi all. I am making some charts and need some help with the cells the chart references as source data. I am trying to make it so that I can insert and delete rows while having the chart reference the original cell, rather then automatically updating and following the cell when I add or remove rows. Basically trying to do the same thing in the chart as I can do with a formula by using the "indirect" feature. Is this possible with a chart? Having to go back in and manually change the references back to the original cells is time consuming each week. Goto Insert - Nam...

Macro to show only rows with X
How would I write a script or macro to display rows that have and x in "A" and hide all other rows? Thank you With data starting in ColA; try Cells.AutoFilter Field:=1, Criteria1:="=x" -- Jacob (MVP - Excel) "Jeremy" wrote: > How would I write a script or macro to display rows that have and x in "A" > and hide all other rows? > > > Thank you Option Explicit Sub Remove_Unwanted_Rows() Dim rng As Range Dim cell, RowArray As Range Set rng = ActiveSheet.Range(Cells(1, "A"), Cells(Rows.Count, &qu...

Data Validation Syntax Question
I'd like to restrict a user (me) from entering more than two digits after the decimal point in a cell. I have a checkbook spreadsheet and recently discovered (by accident) that I made a withdrawal entry of 117.122 a couple of years ago, but I can't figure out how to be sure to keep from doing it again. Am I missing something simple? Thanks for any help. Jim One way: =INT(A1*100)=(A1*100) multiply by 100 and see if it's a whole number If A1 is the activecell. Another way: =MOD(A1*100,1)=0 multiply by 100 and divide by 1. Check the remainder to see if it's 0. Jim &...

Upgrade from Mac Office 2004 to Office X?
Hi everyone, I've been running Mac Office 2004 and I want to upgrade it to Office X. Do I really have to buy the complete version of Office X to do this, or can I just buy the Office X upgrade? The reason I ask is that from what I've been able to research, the Office X upgrade only allows you to upgrade from Office 2001 but not from Office 2004. Am I correct about this? And if I am correct, does anyone know why Microsoft would have implemented such a policy? Thanks in advance to anyone who can help! Kind regards, Jeff Wait! don't answer that. just realized 2004 is the upgrad...

Formula to get data from files #2
The files are all closed, and in various folders. (I could move them al into the same folder to make things easier though -- greasybo ----------------------------------------------------------------------- greasybob's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=792 View this thread: http://www.excelforum.com/showthread.php?threadid=26321 ...

COUNT /COUNTIF for Multiple Column Defined Dynamic Named Range
Hi, Would like to use Dynamic Named Range "RESULTS" in Formula. The Dynami Named Range "RESULTS" spans 30 COLUMNS and many ROWS. Need help with Formula to COUNT specified criteria in each separat COLUMN of the Defined Dynamic Named Range "RESULTS". Looking for on Formula that can be adapted with an OFFSET / INDEX to work on the othe COLUMNS. Example Results required from Formula: COLUMN 1 in Dynamic Named Range "RESULTS" - COUNT instances of numbe 75 COLUMN 2 in Dynamic Named Range "RESULTS" - COUNT instances of numbe 75 COLUMN 30 in Dy...

copy formula down a whole column?
I probably ought to know this, but: How can I copy a formula that will increment as necessary (A2, A3, A4 etc) down a whole column. Currently I am dragging the corner down so far and then having to remember how far I've gone. How should I really be doing it folks? Be gentle, Steve_H If you have data in an adjacent column, then you can just double-click the fill handle on the cursor. This is the small black square in the bottom right corner, and it will fill down for as many entries as you have in the column to the left. Hope this helps. Pete In addition to Pete's d-click...

active X control
I have a calandar (active x control) on my form. Does anyone know how to link/sync it to a bound control; i.e., when a user chooses a date control, the calandar appears, and when a date is selected it populates into the requisite field. alex Try this: http://www.datastrat.com/Download/XCalendar2K.zip -- Arvin Meyer, MCP, MVP http://www.datastrat.com http://www.mvps.org/access http://www.accessmvp.com "alex" <sql_aid@yahoo.com> wrote in message news:1182963470.109302.283780@q75g2000hsh.googlegroups.com... >I have a calandar (active x control) on my form. > Does anyo...

Getting query data for a report
If I have the query "UniqueCounts" and following query results: Count ServicesType 2 Food Basket 99 Men's Dorm 84 Personal Needs 5 PN Family Pack 14 Program Dorm 7 Temporary ID What would I put in a text field in a report to get the query results from Count for the ServicesType "Men's Dorm". (Which should be the value 99.) Thanks! you would want to put in 'Men's Dorm' as the criteria in the query....on in a new query that is applied to this query ...

Macro to change X-axis scale has errors
I've read and studied code from a number of posts on this subject but my "newbie" macro code has errors. I have the minimumscale value and maximumscale value (entered by the user) stored in Sheet1 at $E$2 and $F$2. My chart is in Sheet2. This is my complete macro: Sub ChangeXAxisScale() ' ' ChangeXAxisScale Macro ' With Worksheets("Sheet2") With ActiveChart.Axes(1, 1) .MinimumScale = Range("$E$2").Value .MaximumScale = Range("$F$2").Value End With End With End Sub I know I am missing s...

unable to use ip literals user@[x.x.x.x]
hello everybody. my exchange server 5.5 had an open relay which has now been fixed. but the IP address of my mail server got listed in www.dsbl.org. and now i am not able to send mails to our branch office which uses the list of www.dsbl.org to get rid of spam mails.now i need an address postmaster@[x.x.x.x] to receive a confirmation email from dsbl.org. i did everything that the following miscrosoft site says. http://support.microsoft.com/default.aspx?scid=kb;en- us;193316 but still i am not able to receive mails at postmaster@ [x.x.x.x]. i tested this by sending a mail to this ID ...

Office v.x Word
When creating mailing labels I am unable to print. I can preview page layout under data merge manager and print the page. But when I complete the merge to new document and then print it seems to be sending somewhere other than my printer. This merge file was created in word 98. I recreated in v.x and it still does not print the labels. Could it have something to do with entrouge. Help please!!!! I am new to OSx I just upgraded from 0S 8, I know behind the times. Thanks, Signal Hi When you use File > Print you can select which printer (if you have more than one) from the dialo...

Importing data from Access and creating statistics and a pie chart
I have an Access database field that contains the name of a part, which is one of 93 parts. I want to be able to import this data to Excel so that I can statistically show the numbers of each type of part sold within a specific time frame. For instance, we sold 25 widgets last quarter which is a 15% increase over the previous quarter. The imported data would be broken down by part name, and then converted to a pie chart. I think I know how to limit the time frame in Access, but am lost when it comes to importing the data and generating the statistics in Excel and creating the pie char...

Active X Pop-up???
Blank How does one delete MS Internet Explorer "Active X" pop-up message?? Thnxxxx........Rudy By asking in an IE newsgroup, perhaps? "Rudy" <rucan70@comcast.net> wrote in message news:hrupjk$tnn$1@news.eternal-september.org... : Blank How does one delete MS Internet Explorer "Active X" pop-up : message?? Thnxxxx........Rudy : : Rudy wrote: > Blank How does one delete MS Internet Explorer "Active X" pop-up > message?? Thnxxxx........Rudy What did you find when reviewing the ...

Pivot table
The item i want in my data field is text (words). I do not want a count or a sum of this data. Is there any way to see text data in a pivot table? It is possible to show text fields as first, second, third... item in Rows or Columns. I never had them in Data section. -- Topola Not in the data fields--they're numeric summaries. You can put them in the row/column or page field, though. pum75 wrote: > > The item i want in my data field is text (words). I do not want a count or a > sum of this data. Is there any way to see text data in a pivot table? -- Dave Peterson ...

Re-entering data into a Database
I have constructed a database and enter data three different times. For instance columns A-F are entered initially and conlumns G-Q are entered at later dates but must correspond with an existing inventory number. From using and index and nested matching funciton I have found the cells for which I need to enter the data such as "G7". However in writing my Macro I don't know how to place my cursor in this cell, since it changes with each entry, for instance the next entry may go in "G22", "P9", and so on. I am thinking it might require a loop starting ...

Using a dll to generate report data
Hi All I am assessing whether we should move to reporting services to make available various company reports via the web. While most reports have a stored procedure origin for their data, there are a few of our reports that require some programatic massaging. These reports typically involve some specialised data pivoting and/or formatting. My question is, can reporting services utilise a custom dll as the source of data for a report? As an example, reporting services can generate a report from a stored procedure via a select statement. Can reporting services build a rep...

Question about graphing four sets of data
So i have four columns of data. The first column contains just names. I want the names to appear as the x-axis. The second column is slowest time, the third is fastest time (in min) that each name ran. Time in minutes will be on the y-axis. Up to now, it's pretty straight forward. Just make a bar chart with the name data and time data and there you go (two bars for each name). But I also have this fourth column of data which is "Number of attempted tries" or simply sample size. I want to place the sample size as a number on top of the bars. Any ideas? -- RawlinsCross...

how can we transfer data from child dialog box to parent Dialog Box. #2
hi guys, i have created modalless child dialog box by parent dialogbox using UI Thread . now i want to do 2 things first i want to transfer CPoint variable from child to parent and second i want to call OnLButtonDown() function of parent DialogBox. i have hint but not proper please help me to complete this one. SendMessage(m_pParent, WM_LBUTTONDOWN, x,y); // from child side void CHRS_MoleDlg::OnLButtonDown(UINT nFlags, CPoint point) //on parent dialog box first of all tell me is it possible if yes then what will be "x" and "y" so that they can convert into CPoint v...

Framework X error
I am getting this message when I open Office X programs: "An unexpected error occured while trying to load the Microsoft Framework X library" There are users on the ibook. This happens under student where much is locked out. I checked permissions for the user data folders in the users Document folder and these are ok. Any Ideas? Thanks in Advance norm On 8/6/03 1:08 PM, in article 6c46d36e.0308061208.4faa16ce@posting.google.com, "Norm" <gwaltnen@mail.ips.k12.in.us> wrote: > I am getting this message when I open Office X programs: > > "An unexpecte...

Data Table
Dear all, I want to create a data table. Data>Table But coming a message "Selection is not valid"/"Input cell reference not valid", Plz help to create One variable and two variable data table. The data table needs to be set-up on the same sheet as the base formula (it doesn't work across sheets) I have 2* samples on data table in my free archives: *one variable data table, 2 variable data table One variable data table example.xls http://savefile.com/files/1007074 2 variable data table example.xls http://savefile.com/files/46276 The samples contain th...