• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Clubbing the lines

Hi all,

This is Surya, from Hyderabad, India. This is my first day in the forum. I have been working in Finance of a construction company. Normally I receive data from all site accountants who are spread all over India in different formats and my job is to consolidate the data in a standardized format and file the statutory returns.

I have been facing this problem for so long. The excel file which I receive from colleagues contain invoice information. If the invoice is prepared for different items, then the person prepares excel data as per the invoice. Mean to say, for same invoice number multiple rows will be created with multiple values. Example: Invoice no: 896, total value is 24,31,625. But it is split into 2 rows. There will be some invoices which are split into 10 or more rows.

Now my problem is I have to prepare a statement in which I want total invoice value in a single row. To do that I have been using subtotal formula, apply filter on invoice number and copy + paste special the value in standard format. For N number of invoices I have to this operation N number of times.

The solution I want is all such invoices in a particular sheet (Master Sheet) which are entered in multiple rows have to clubbed and shown in a single in separate sheet (Output Sheet)
 

Attachments

  • Chandoo_290715.xlsx
    19.1 KB · Views: 1
Hi suryasabniveesu,

When you "compress" multiple rows into a single row, then you need to know what "rule" you want to apply when the two values are not equal.
For instance: in the field "ITEM-DESCRIPTION" you have "HT TOWER" and "MS TOWER"... you cannot sum these two values, so what do you want to do?
What about the field "RATE"? Do you want to sum, average, count... ignore??

Anyway, I tried something. Please have a look at attached file.
 

Attachments

  • Chandoo_290715.xlsx
    25.9 KB · Views: 0
Hi,

While working on the file, i came up with a question, what would you need for Item Description, UOM & Rate Columns...when you looking to consolidate the details basis Invoice number...

Example: Invoice number - 00443

Please clarify..?
 
Hi suryasabniveesu,

When you "compress" multiple rows into a single row, then you need to know what "rule" you want to apply when the two values are not equal.
For instance: in the field "ITEM-DESCRIPTION" you have "HT TOWER" and "MS TOWER"... you cannot sum these two values, so what do you want to do?
What about the field "RATE"? Do you want to sum, average, count... ignore??

Anyway, I tried something. Please have a look at attached file.

Dear Xiq,

Thank you very much for the info. Sorry for not mentioning anything about "Item-Description" and "Rate" in my question. We have to simply ignore those two columns.

As I am completely novice in Index function it took a while to understand, but its working fine. Don't mind I have to type the formulas every month as I will be getting data from different sources. But really thank you so much your effort has given me some idea about MATCH and INDEX functions which we rarely use or don't know how to use :)

In fact some other friend advised pivot table as another solution.

Thank you so much for your fast reply.

Regards,
Surya
 
Hi,

While working on the file, i came up with a question, what would you need for Item Description, UOM & Rate Columns...when you looking to consolidate the details basis Invoice number...

Example: Invoice number - 00443

Please clarify..?

Hi Asheesh,

We can ignore Item Description, UOM & Rate Columns...

Regards,
Surya
 
Dear Xiq,

Thank you very much for the info. Sorry for not mentioning anything about "Item-Description" and "Rate" in my question. We have to simply ignore those two columns.

As I am completely novice in Index function it took a while to understand, but its working fine. Don't mind I have to type the formulas every month as I will be getting data from different sources. But really thank you so much your effort has given me some idea about MATCH and INDEX functions which we rarely use or don't know how to use :)

In fact some other friend advised pivot table as another solution.

Thank you so much for your fast reply.

Regards,
Surya
Yes, I was also first thinking of the Pivot Table (good option by the way). However it requires some repetitive steps that I simply was trying to avoid ;)
 
Hi,

Indeed, pivot is a good option...however, see the attached for formulae based solution
 

Attachments

  • Chandoo_290715.xlsx
    23.9 KB · Views: 1
Back
Top