Rearranging Datewise horizontally in a specified format from Raw data

Hi,

As enclosed in my sample data , I have a Data Sheet in which raw data exist.From this data,I want to arrange data in the format which is shown in "Result"sheet.Following things should be extracted

Column A (Date) of result sheet should be extracted from Column A(Date) of Data sheet

Column B (Name of Party) of result sheet should be extracted from Column C (Particulars) corresponding to the entry of Column I(Credit) of Data sheet

Column J (Total) of result sheet should be extracted from Column I (Credit) of Data Sheet & must be corresponds to Column B (Name of Party) of result sheet.Only numeric data should be taken.

Column C (Particulars) of result sheet should be extracted 1 row below of "as per details" of Column C from Column C(Particular) of Data sheet

Column E (Taxable Purchase) of result sheet should be extracted from Column H (Debit) of Data Sheet with a condition of that it corresponds to the Column C
(Particulars) of result sheet & it does not contains 'pp' in the sentences that is displayed in every entry in Column C just before date of Column A.It should contain only numeric data.

Column F (Taxable Import) of result sheet should be extracted from Column H (Debit) of Data Sheet with a condition of that it corresponds to the Column C (Particulars) of result sheet & it does not contains 'Bill No' in the sentences that is displayed in every entry in Column C just before date of Column A.It should contain only numeric data.

Column G (Vat) of result sheet should be extracted from Column H (Debit) of Data Sheet with a condition of that it corresponds to the Column C (Particulars) of result sheet.It(Column H) is the amount that is just right to "as per details" of Column C of Data Sheet .

Column H (Vat in Custom) of result sheet should be extracted from Column H (Debit) of Data Sheet with a condition of that it corresponds to the Column C (Particulars) of result sheet.It(Column H) is the amount that is just right to "as per details" of Column C of Data Sheet .

Column I (Excise Duty) of result sheet should be extracted from Column H (Debit) of Data Sheet with a condition of that it corresponds to the Column C (Particulars) of result sheet.It(Column H) is the amount that is just right to "as per details" of Column C of Data Sheet .It should be noted that 'Excise Duty' name is itself is present in Column C of Data sheet,hence accordingly it should be done.

Column D (Reference No.) of result sheet should be taken from Column C (Particulars).The data should be extracted that contains " Bill No...." or " PP no...". with suffix numeric value to every " Bill No...." or " PP no..."

Hope this would provide sufficient information.This(Result Sheet) is simply,the breakdown in horizontal form datewise of every entry that is shown datewise in Data Sheet.


With Best Rgds,
Suresh

Pls find enclosed in attachment.

With Best Rgds

For row number 297 you have two entries in I column(299 & 300).Does both the entries should appear for same date.It is creating some problem to write formula.

Hi,

For row number 297 you have two entries in I column(299 & 300).Does both the entries should appear for same date.

Yeah this should appear on the same date.Just move the value of I column of row 299 by inserting an additional column in between column I & column J of my Sheet Result(Revised) with heading as TDS.Remaining row 300 as usual like others.

In otherwords,
I have overlooked,you can just insert that data in my sheet result(revised)after Column I (Excise Duty) i.e in between Column I (Excise Duty) and Column J (Total) of my revised sheet,so that I can get TDS A/c amount also.Move column J (Total) to Column K (Total) .And now Column J (TDS) would appear.

If further info is required then do let me know.
With Best Rgds,

Hi,
Thanks for the reply,

You are requested to add two more column data in the result sheet.One column as stated earlier was that of TDS from Column I (credit)of Data sheet into result sheet as mentioned in post #8-9
For row number 297 you have two entries in I column(299 & 300).Does both the entries should appear for same date.

Yeah this should appear on the same date.Just move the value of I column of row 299 by inserting an additional column in between column I & column J

Secondly,which offcourse I did not mentioned in all my post from beginning is that Column G of Data Sheet.This you can do at the end of all column or anywhere in result sheet.

Lastly, would you like to revised all the formula for google docs spreadsheet.I have tried but it does not work.If possible then kindly do,otherwise do rest of two queries.
After this I will go minutely,if any further changes are required or not or any queries are there to be asked or not.

You did a great work.

With Best Rgds,
Suresh

Originally Posted by hemesh

Please upload a new workbook with addition to be made and results expected. Moreover I dont know how to convert the formulas for google docs that you can search over internet or else can take the advantage of Commercial services provided by our forum.

Hi,

I have enclosed in attachment.The formula where you have made in "Result"worksheet,there I inserted One column i.e D with expected results in few rows extracted from "Data" sheet and another column i.e L with expected result in row 75 of L column i.e L75 from "Data" sheet but it should be noted that since this is the sample workbook,there might be a large number.

Well I am trying for google docs forum too to get the expected result based on your hardwork.As I am trying to use the combination of both.

Hope you have understood.

With Best Rgds,
Suresh

Originally Posted by hemesh

I will have a look at it

Ok I will be eagerly waiting for your reply.

With Best Rgds,

Suresh

Hi Hemesh,

Thanks a lot.You did a splendid work.I am marking this thread as solved.However,I have posted in a next thread regarding shortening of Reference No. column formula of Result sheet that you provided.Anyone might make short without alteration in result.

Anyhow thanks again.

With Best Rgds,
Suresh

OK I will try.

Hi,
Let me elaborate where I am wrong in my syntax or understanding-

Syntax= iferror((................),"")....................is already cleared to me.

Secondly,
Syntax =INDEX( array, row_number, [column_number] )....................here it is confusion to me
Here, array = Data!$A$4:$A$395
row_number = SMALL(IF(Data!$A$4:$A$395<>"",ROW($A$4:$B$395)-ROW($A$4)+1),ROWS($A$1:A1))
Simplification of "row_number"
Syntax = small ( array,k)

here this array = ?????? should this be= IF(Data!$A$4:$A$395<>"",ROW($A$4:$B$395)-ROW($A$4)+1) as it brings =1,5,9,13,18,22,26,30 i.e

{1;FALSE;FALSE;FALSE;5;FALSE;FALSE;FALSE;9;FALSE;FALSE;FALSE;13;FALSE;FALSE;FALSE;FALSE;18;&.............. by using Ctrl+shift+enter(CSE)

k = ROWS($A$1:A1) = 1 and on subsequent row it brings 1,2,3,4,5,6,7 & so on.

Now when I use this function small (array,k) using helper column, it is getting error.

I have enclosed an workbook at the last sheet "Breakdown of formula",I have used 5 helper column where 5th is final result and 4 helper column are the breakdown of your formula.Kindly have a look into it, where I am wrong ?

With
Best Rgds