Index() & Match() or just an if() Statement?

**JustMe602** · 12-11-2006, 06:51 PM

Okay I have lots and lots of info in Sheet 1 that has the data vertically. Also I have a column A that has a unique number that identifies each set of data. The data type in column B is consistant and repeating the info/DATA is the ever changing.

HTML Code:

How do I write a formula that will give me what I need.

**tuph** · 12-11-2006, 07:26 PM

Originally Posted by JustMe602

But I would like the data Horizantally:

Select the range of cells you wish to rearrange and copy. Then in cell A1 of a blank worksheet click on Edit, Paste Special, Transpose.

**JustMe602** · 12-11-2006, 07:29 PM

I have 6,481 rows of data in sheet 1 and I would have to do this 324 times (the number groups of data) and then that is not a dynamic way of solving my problem.

Thanks for the response.

**VBA Noob** · 12-11-2006, 07:43 PM

Hi,

If your data in Col A and B enter this in C1 and drag to D1 etc and drag down to C2 and D2 etc

=IF(OFFSET(INDIRECT(ADDRESS(1,ROW(),4)),COLUMN()-3,0)=0,"",OFFSET(INDIRECT(ADDRESS(1,ROW(),4)),COLUMN()-3,0))

VBA Noob

**Maistrye** · 12-11-2006, 07:49 PM

I know you didn't say it this way, but the following formula assumes that you are putting your data starting in cell E2, F2, etc...

It also assumes that your column B data is starting in B1. It also assumes your data is regular, ie. there are (in my example) 20 separate headings that have to be filled in. ie. Exactly 20 rows/line. (I'm slightly skeptical of this, as 324 does not divide 6481 exactly, but it will be up to you to rectify.)

Please Login or Register  to view this content.

Just drag it across and then drag down.

You'll probably want to Copy and Paste Special -> Values afterwards.

Scott

**JustMe602** · 12-12-2006, 03:33 PM

I have attached a spreadsheet that has what I am trying to protray.

I have the data on Sheet 1 and trying to transpose the data to the format in Sheet 2.

Thanks all help is apprieciated.
JustMe.

**Maistrye** · 12-12-2006, 04:24 PM

Ok, I put the formula on the first 3 lines of Sheet2. (Modified to allow for blank cells)

You can see the correspondence by looking at the values I put in Sheet1, Column C.

Drag to the bottom of your data... then I'd recommend Copying the whole thing and using Paste Special -> Values.

Scott