To create the required view

Are you still using Excel 2007? If not, please update your profile.

Done. Updated the profile.

It will not be easy to produce that format with formulae. Would this be OK for you?? This is MUCH easier to do!!

If this presentation is ok, then Power Query is the answer.

Excel 2016 (Windows) 32 bit

	A	B	C	D
1	Company Id	Company Name	Total Amount	Service Id
2	1001	A1	1500	100069
3	1001	A1	1500	100066
4	1001	A1	1500	100513
5	1001	A1	1500	100503
6	1001	A1	1500	100507
7	1002	A2	18900	100069
8	1002	A2	18900	100066
9	1002	A2	18900	100513
10	1003	A3	23657
11	1004	A4	8975	100069.1001
12	1005	A5	67463	100513
13	1005	A5	67463	100503
14	1005	A5	67463	100507
15	1006	A6	975	100069
16	1006	A6	975	100066
17	1006	A6	975	100513
18	1006	A6	975	100503
19	1007	A7	78585	100069
20	1007	A7	78585	100066
21	1007	A7	78585	100513
22	1007	A7	78585	100503
23	1007	A7	78585	100507
24	1007	A7	78585	100456

Sheet: Table1

Please Login or Register  to view this content.

Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.

- Follow this link for an introduction to Power Query functionality.

- Follow this link for a video which demonstrates how to use Power Query code provided.

Administrative Note:

We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not provided the required cross-post link(s) here.

Please see Forum Rule #3 about cross-posting and adjust accordingly. Read this to understand why we (and other sites like us) consider this to be important.

(Note: this requirement is not optional. I will provide the link for you this time: https://chandoo.org/forum/threads/ho...is-view.46508/.)

Please acknowledge that you have read and understood post #7.

I missed the bit about 10,000 rows. Go with the PQ solution (Post 6). It is your best option.

Originally Posted by alansidman

If this presentation is ok, then Power Query is the answer... .

Thanks for the links. I have gone through these and trying to implement the same. I learned something new today. Thanks again!

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.