Create Grid in Excel 1

[Mungovan]

Hi.
This is my problem:

I have three colums in an Excel spreadsheet. Column 1 contains 100 id's ranging between 1 and 500.

Coulumns 2 and 3 contain X and Y co-ordinates (between 1 - 50) respectively, that correspond to the id's.

Is there anyway of creating at 2-D grid (50x50) that contains the id's at their repective co-ordinate positions.

Thanks a million for any help anyone can give me.

Thanks,
D

 

[Zathras]

This can be done if you have some flexibility in the formats of your worksheets.

The technique makes heavy use of the VLOOKUP function. If you are not familiar with it you may wish to review the Excel documentation first.

Here goes:

1) In the 3-column spreadsheet, we need two more columns:

Formula in cell D2: =B2&"-"&C2 Copy to all rows
Formula in cell E2: =A2 Copy to all rows
Name the sheet IDS
(Rename it to anything else when finished if you wish.)

2) In the 2-D sheet (new sheet in same workbook):

Put the numbers 1 thru 50 in cells B1 thru AY1, and
put the numbers 1 thru 50 in cells A2 thru A51.

Paste this formula in cell B2:

=IF(ISNA(VLOOKUP($A2&"-"&B$1,IDS! $D$1:$E$101,2,0)),"",VLOOKUP($A2&"-"&B$1,IDS!$D$1:$E$101,2,0))

Copy the formula from B2 into all of the other cells
in the sheet (B2..AY51)

Note1: The formula references sheet IDS in the same workbook. If you rename the IDS sheet, the formulas will automatically update.

Note2: You may wish to put 0 instead of "" for empty cells in the 2-D grid. If so, use Tools/Options and uncheck the "Zero values" box on the View tab.

Note3: You could simplify a bit if you don't need the ID's in column A. Use column E directly.

 

[Zathras]

Sorry, I just noticed an extraneous space was inserted in the middle of the formula. The correct formula for cell B2 is

=IF(ISNA(VLOOKUP($A2&"-"&B$1,IDS!$D$1:$E$101,2,0)),"",VLOOKUP($A2&"-"&B$1,IDS!$D$1:$E$101,2,0))

 

[Mungovan]

Cool!!

It's workin' grand, thanks a million!!!


D