VBA to LIST information

[MsAlitaY]

Hello All,

I have a spreadsheet setup as follows:

ColA- ColB- ColC- ColD- ColE-
Row1- Blank- Blank- 722- 722-
Row2- Blank- Blank- 10- 10-
Row3- Blank- Blank- 00- 00-
Row4- Blank- Blank- 389- 389-
Row5- Blank- Blank- 02- 03-
Row6- Blank- Blank- 100- 100-
Row7- 1111- Description1- 2,000- 10,000-
Row8- 1112- Description2- 52,000- 5,000-
Row9- 1114- Description3- 100,000- 200,000-
Row10- 1115- Description4- 9,000- 30,000-
Row11- 1116- Description5- 15,500- 9,000-

The information continues like this across columns with the info in rows 1-4 remaining the same and rows 5-### possibly containing different information.

I need to organize the data as follows:

72200 1000 389002 100 1111 2,000
72200 1000 389003 100 1111 10,000
72200 1000 389002 100 1112 52,000
72200 1000 398003 100 1112 5,000

Etc..... through the end of the data. I had thought about using index, match in VBA format but I think I am missing a piece.

Any suggestions?

 

[Rijnsent]

Hi MsAlitaY,
generally I would use OFFSET combined with ROW/COLUMN and basic mathematics to get this kind of jobs done. Let me elaborate a bit:
-assuming you have your data on a sheet called DATA and the desired output on a sheet called OUTPUT
-cell A1 of your output (72200) is basically cell D1, or better: R1C4 (row 1, column 4)
-cell B1 (1000) looks like R2C4 & R3C4 ?
-etc.
-Next, add a couple of "helper" columns in your OUTPUT sheet, like: =ROW(), =MOD(ROW(),3) etc. to get the right offsets.
-with the helper columns you can create the right OFFSET formula for every column of desired output.

Hope that helps, otherwise please do post some more details on the desired output (which input cells are used to create the output).

Cheers,

Koen