How to copy paste a series of data in rows

[Vijay Joshi]

I have an excel output from Oracle that is absolute Garbage...Data that could be in rows is unfortunately repeated verticallly making it impossible to analyze.
For Instance
In Row 2 Header is Description(B2);Start Date (C2); End Date(D2); Currency Code(E2); Active Flag(F2)... Then in Row number 3 there is one liner information under heach Header..
After one blank row in Row 6 again headers continue :Contract Name(A6); Description(B6); Start Date(C6); End Date(D6); Currencey Code(E6); Precedence(F6); .. Then Row Number 7 there is one liner information under each header..
Row number 10 has again headers viz; Division(A10); CustomerNumber (B10); Customer Name (C10); Status (D10)..
But underneath there is a data which could be one liner or 10 liner...

These headers keep repeating for another 4000 rows to give the details of say 500 odd contracts...
I WANT TO GET ALL HEADERS IN ONE ROW.. one contract in one line.. Any formula help in here.. I am learning VBA and Macros but very very basic right now... Thanks Vijay

 

[Hui]

Vijay

Firstly, Welcome to the Chandoo.org Forums

Can you please post a few rows of data and an example of what the output should look like
You can attached files to your post using the "Upload a File" Button below

 

[Vijay Joshi]

Hi Hui,
Thanks a lot for coming back quickly on this. As you may see in teh attachement there are two contracts spread over rows one under another. What I am after is to line each contract horizontally.. some of the information is spilling over the rows again and again....
Thanks
Vijay

 

[SirJB7]

Hi, Vijay Joshi!

Let me say that the file you posted doesn't look at all as absolute garbage, in fact is a very clear and smart... report. So my advice is don't try to steal (read as extract) data from wrong sources (reports) and do it instead from right sources (queries, data exports, file transfers or however someone names this), asking IT guys or support team or whomsoever to provide you with the right tool.

Well, without VBA but with 2 helper columns and a help row, deal?

Give a look at the attached file. It uses 3 named ranges defined as follows:
OracleTable: =DESREF(XXCGL__Customer_Contracts_Repo_!$A$1;;;FILAS(ContractCountList);COLUMNA(ContractCountList)-1) -----> in english: =OFFSET(XXCGL__Customer_Contracts_Repo_!$A$1,,,ROWS(ContractCountList),COLUMN(ContractCountList)-1)
ContractCountList: =DESREF(XXCGL__Customer_Contracts_Repo_!$O$1;;;CONTARA(XXCGL__Customer_Contracts_Repo_!$O:$O)-1;1) -----> in english: =OFFSET(XXCGL__Customer_Contracts_Repo_!$O$1,,,COUNTA(XXCGL__Customer_Contracts_Repo_!$O:$O)-1,1)
CustomerCountList: =DESREF(XXCGL__Customer_Contracts_Repo_!$P$1;;;CONTARA(XXCGL__Customer_Contracts_Repo_!$P:$P)-1;1) -----> in english: =OFFSET(XXCGL__Customer_Contracts_Repo_!$P$1,,,COUNTA(XXCGL__Customer_Contracts_Repo_!$P:$P)-1,1)

Source formulas:
O1: =CONTAR.SI(A$1:A1;"CONTRACT") -----> in english: =COUNTIF(A$1:A1,"CONTRACT")
P1: =COINCIDIR(O1+1;O:O;0)-COINCIDIR(O1;O:O;0)-10-2 -----> in english: =MATCH(O1+1,O:O,0)-MATCH(O1,O:O,0)-10-2
Cell in column A, row equal to end of report plus 2 (e.g. A37): CONTRACT
Copy down O1: P1 thru this row.

Target formulas:
Row 1: group titles
Columns A:F, contract data: A1: CONTRACT
Columns G:L, secondary price list data: G1: SECONDARY PRICE LIST
Columns M:R, customers data: M1: CUSTOMER
Row 2: detail titles
Copy proper titles from 1st worksheet row 2 to A:F range, from row 6 to G:L and from row 10 to M:R
Row 3:
A3: =SI.ERROR(INDICE(OracleTable;COINCIDIR(FILA()-2;ContractCountList;0)+2;COLUMNA());"") -----> in english: =IFERROR(INDEX(OracleTable,MATCH(ROW()-2,ContractCountList,0)+2,COLUMN()),"")
G3: =SI.ERROR(INDICE(OracleTable;COINCIDIR(FILA()-2;ContractCountList;0)+6;COLUMNA()-6);"") -----> in english: =IFERROR(INDEX(OracleTable,MATCH(ROW()-2,ContractCountList,0)+6,COLUMN()-6),"")
Copy A3 thru F3, G3 thru L3, and A3:L3 down as required, i.e., until 1st empty row appears. In the sample file rows with formulas have column A yellow shaded.

If you want to retrieve each 1st customer you can apply similar techniques changing the offsets properly as in A3 and G3 formulas. Model not valid for extracting all the customers: it'll be required a helper column in worksheet Unscrambled and other formulas (out of the specified scope).

Just advise if any issue.

Regards!

 

[Vijay Joshi]

Superb.. thanks Ninja... At this stage I will just use the formulas as is... too complicated for me to decipher .....thanks once again and have a lovely week end.