Its an array formula

So use Ctrl + Shift + Enter once enter the formula.





On Sep 10, 3:25*am, Som wrote:
Input Data,
---------------------

Material * * * * * * * * * * * * * * * * * * *Data
0114A0001 * * * 2800002401 (00010) *-- M791
0114A0001 * * * 2800005992 (00010) *-- M791
0114A0001 * * * 2800005993 (00010) *-- K845
0114A0002 * * * 2800002401 (00090) *-- M791
0114A0002 * * * 2800005993 (00060) *-- K845
0114A0003 * * * 2800001779 (00010) *-- M791
0114A0003 * * * 2800004795 (00050) *-- K845
0114A0004 * * * 2800002401 (00020) *-- M791
0114A0004 * * * 2800005992 (00020) *-- M791
0114A0004 * * * 2800005993 (00020) *-- K845
0114A0005 * * * 2800004795 (00060) *-- K845
0114A0012 * * * 2800002401 (00030) *-- M791
0114A0012 * * * 2800005992 (00030) *-- M791
0114A0021 * * * 2800002401 (00040) *-- M791
0114A0021 * * * 2800005992 (00040) *-- M791
0114A0029 * * * 2800002401 (00050) *-- M791
0114A0029 * * * 2800005992 (00050) *-- M791
0114A0030 * * * 2800004794 (00010) *-- M791
0114A0034 * * * 2800004794 (00020) *-- M791
0114A0034 * * * 2800004795 (00010) *-- K845
0114A0035 * * * 2800001779 (00020) *-- M791
0114A0035 * * * 2800004794 (00030) *-- M791
0114A0035 * * * 2800004795 (00020) *-- K845
0114A0036 * * * 2800002401 (00060) *-- M791
0114A0036 * * * 2800005993 (00030) *-- K845

---------------------------------------------------------------------------*-------------

My required output,

Material * * * * * * * * *Data 1 * * * * * * * * * * * * * * Data 2 * * * * * * * * * * * * * *
* * * Data 3 * *Data 4 *Data 5 *Data 6
.................................................. ..........................*....................... ............................
0114A0001 *2800002401 (00010) *-- M791 *2800005992 (00010) *--
M791 * *2800005993 (00010) *-- K845 * *

0114A0002 *2800002401 (00090) *-- M791 *2800005993 (00060) *-- K845 * *

0114A0003 *2800001779 (00010) *-- M791 *2800004795 (00050) *-- K845

0114A0004 *2800002401 (00020) *-- M791 *2800005992 (00020) *--
M791 * *2800005993 (00020) *-- K845

0114A0005 *2800004795 (00060) *-- K845 * * * * * * * * * * * * * * * * *
0114A0012 *2800002401 (00030) *-- M791 *2800005992 (00030) *-- M791 * * * * * * * * * * * * * *
0114A0021 * 2800002401 (00040) *-- M791 2800005992 (00040) *-- M791 * * * * * * * * * * * * * *
0114A0029 * 2800002401 (00050) *-- M791 2800005992 (00050) *-- M791 * * * * * * * * * * * * * *
0114A0030 * 2800004794 (00010) *-- M791 * * * * * * * * * * * * * * * *
0114A0034 * 2800004794 (00020) *-- M791 2800004795 (00010) *-- K845 * * * * * * * * * * * * * *
0114A0035 * 2800001779 (00020) *-- M791 2800004794 (00030) *--
M791 * *2800004795 (00020) *-- K845 * *

0114A0036 * 2800002401 (00060) *-- M791 2800005993 (00030) *-- K845 * *

---------------------------------------------------------------------------*--------------------- * * * * * * * * *



"Som" wrote:
I require your help seriously (in MS Excel).

I've an Excel file which contains 2 columns (1. Material 2. Data).

In "Material" column, you can find material nos (not unique)
In "Data" column, you can see respective PO nos of those materials.

This is my file as input.

================================================== ===

My requirement,

I want this data to be formatted in different way.

My output excel file will have 7 columns

(1. Material 2. Data1 *3. Data2 4. Data3 5. Data4.............)

In "Material" column, I want to put unique material nos (no repeatations)
In "Data1 ....Data6" column, I want to put respective PO nos.

================================================== ===

Is there any function or procedure to the job automatically?

Pls find the attached Excel file (Excel Task.xls) for better understanding.

Sheet ="Raw Data (Input)" is my input
&
Sheet ="Required output" is the result I want.

I've given you the test data of 26 rows, but actually it comes in huge
volume of 5 - 6000 rows.

If required, I can send you live data also.

Please help me to get rid of this problem.(my mail id
)- Hide quoted text -

- Show quoted text -