How to convert........

  • Thread starter Thread starter Som
  • Start date Start date
S

Som

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
(e-mail address removed))
 
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
 
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
Click to expand...
 
Try this one

Sheet 1 - u have input data.

Col A - Material and Col B - Data

In sheet 2 - Output

A1 - Material
B1- Data 1
C1- Data 2
D1- Data 3
E1 - Data 4
.....

Unique materials use advance filter to in sheet 1 Col A , u will get
unique materials. Copy that and paste it in Sheet 2 Col A..
In sheet 2 (data 1) B2 put this formula
=INDEX(Sheet1!$A$1:$B$14,SMALL(IF(Sheet1!$A$1:$A$14=A2,ROW(Sheet1!$A
$1:$A$14)),ROW($1:$1)),2) and drag it down.

Similarly in C2 put this formula ( same as above only last part bit
change)
=INDEX(Sheet1!$A$1:$B$14,SMALL(IF(Sheet1!$A$1:$A$14=A2,ROW(Sheet1!$A
$1:$A$14)),ROW($2:$2)),2) and drag it down .

Similarly for the other columns...
 
madhu,

thanks, but unable to understand...............

Input

Material No Data

a1 0001
a1 0002
a1 0003
b1 0004
c1 0004
c1 0001
c1 0002
c1 0005
d1 0006
d1 0003

my required output :-

material No Data1 data2
data3 data4
a1 0001 0002
0003
b1 0004
c1 0004 0001
0002 0005
d1 0006 0003


can u do that ?

i can mail u the excel file, if u want.............(if required , pls give a
test mail in (e-mail address removed))

waiting...............
 
madhu,

I've tried , but it is not working.........

I've again tried to post output format , but it is getting scattered......

can I mail u?......give me test mail........
 
I have tested that one, its working fine for me ?
mail id - (e-mail address removed)

I cant replies to u immediately because i am in office, i dont have
access for personal mail box.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

How to select values in sequence order? 3
Subtotaling one sheet on another sheet 3
conditional formatting for more than 3 conditions 3
Conditional copy 1
Splitting data in a cell 9
Compare 2 worksheets 1
Print Command 1
Removing empty cells from spreadsheet data 1

Back
Top