Looking for a solution to this matrix

[shaikhrulez]

Hello,

Please refer to the attached file.

Is this possible to transform this matrix in column (vector) format? In attached file, top row contains branch location codes, and first column contains account code. I want to mention branch location codes in front of each field beside account code.

 

[Hui]

I read it that you wanted:


If so:

Add a new worksheet
A1: =TEXT(OFFSET(Sample!$A$1,0,INT((ROW())/296)+1),"000#")&"-"&OFFSET(Sample!$A$1,(ROW())-INT((ROW())/296)*295,0)
B1: =OFFSET(Sample!$A$1,(ROW())-INT((ROW())/296)*295,INT((ROW())/296)+1)
Copy down to row 25075

or refer attached file:

 

[shaikhrulez]

I read it that you wanted:
View attachment 23143

If so:

Add a new worksheet
A1: =TEXT(OFFSET(Sample!$A$1,0,INT((ROW())/296)+1),"000#")&"-"&OFFSET(Sample!$A$1,(ROW())-INT((ROW())/296)*295,0)
B1: =OFFSET(Sample!$A$1,(ROW())-INT((ROW())/296)*295,INT((ROW())/296)+1)
Copy down to row 25075

or refer attached file:

Thank you so much @Hui Sir. you saved my day, otherwise I would've been working entire day to get this done.

I wanted the account code on a separate column as well, but that's not a big deal anymore; did this through text to column.

Sir, it will be nice if you could spare some time in explaining the formula for me & other excel geeks..

Thanks

 

[shaikhrulez]

@Hui Sir while working on the file I encountered a problem, I guess there is something missing, the formula you posted isn't fetching up the entire record as it should.

For example, look for Account Code 1208, the total number of amounts fetched should've been 63 for all the 85 branch location mentioned in first 1st row.

Is there anything I'm missing out here? Please check.

Thanks

 

[Hui]

Whoops:

A1: =TEXT(OFFSET(Sample!$A$1,0,1+INT((ROW()-1)/295)),"000#")&"-"&OFFSET(Sample!$A$1,MOD(ROW()-1,295)+1,0)
B1: =OFFSET(Sample!$A$1,MOD(ROW()-1,295)+1,1+INT((ROW()-1)/295))

Copy down

or see attached file:

 

[shaikhrulez]

Whoops:

A1: =TEXT(OFFSET(Sample!$A$1,0,1+INT((ROW()-1)/295)),"000#")&"-"&OFFSET(Sample!$A$1,MOD(ROW()-1,295)+1,0)
B1: =OFFSET(Sample!$A$1,MOD(ROW()-1,295)+1,1+INT((ROW()-1)/295))

Copy down

or see attached file:

Thanks @Hui for the quick response. It helped me a lot saved my hours, I think it couldn't have done better than this...