Report issues (Data from different columns)

[sxmajmda]

	A	B	C	D	E	F	G	H	I
1	I want D1 in A1	I want H1 in B1		F350IR				1
2	I want D2 in A2	I want H2 in B2		F350CP				1
3	I want D3 in A3	I want H3 in B3		F350CP				1
4	I want E4 in A4	I want G4 in B4			F010AC1101		1
5	I want E5 in A5	I want G5 in B5			F010AC1101		1
6	I want E6 in A6	I want G6 in B6			F010AC		1
7	I want F7 in A7	I want I7 in B7				F247ST1111			1
8	I want F8 in A8	I want I8 in B8				F247ST1111			1
9	I want F9 in A9	I want I9 in B9				F247ST1111			1

<colgroup><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

So what happens is, when I convert my report to excel, the data structure is intact, but Col A. data can be in Col. A or in Col. B or Col. C....
I have 9 columns of data in the report and it can span over 40 columns, so what I want to do is add 9 columns at the beginning, now I can want to do some kind of index, so that first data from Col. 10 (Col. J, if I am counting correctly) should come to Col. A, now if there is data in column K, it should come to Col. B....if there is no data in Col. K, then it should look for which ever column (in the same row) has some data and pull it to Col. B and so on....hope my question makes sense.

Thanks in advance,

 

[ca.moazzam]

This can be done using formulas but let's first have a look at your data file to see if this data can be extracted in the right columns in the first place. Can you share the original data file from which you are extracting this data in to Excel?

 

[sxmajmda]

That will be a problem, if you can give me some pointers as to which formulas you would use, may be I could give it a try

 

[sxmajmda]

When I get a report out of the system, “column a” should have all the names, instead it is first few rows name is in “column a”, then it is in “column b” then in “column c” and so on….
So I want to add a column before “column a” and in the “new column a” I want to get data from all the other columns…hope it makes sense…

Name			Age
John			30
James			32
Mary			28
Aaron			31
	Bill		26
	Joe			34
	Pete				37
		Ashley			32
		Nolan			26

<tbody>
</tbody>

So once I add the new columns it should appear something like this

Name	Age	Name			Age
John	30	John			30
James	32	James			32
Mary	28	Mary			28
Aaron	31	Aaron			31
Bill	26		Bill		26
Joe	34		Joe			34
Pete	37		Pete				37
Ashley	32			Ashley			32
Nolan	26			Nolan			26

<tbody>
</tbody>

Can anyone help me with this?