| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to convert........
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 ) |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to convert........
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 ) |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to convert........
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 ) |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to convert........
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$1 4=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$1 4=A2,ROW(Sheet1!$A $1:$A$14)),ROW($2:$2)),2) and drag it down . Similarly for the other columns... 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 - |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to convert........
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 - |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to convert........
|
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to convert........
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........ "muddan madhu" wrote: 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 - |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to convert........
|
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to convert........
|
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| can the "convert" function in excel convert to UK gallons? | Excel Discussion (Misc queries) | |||
| how will i convert 05.01.2007 convert 05.Jan.2007 format? | New Users to Excel | |||
| convert value in word. For Exampe Rs.115.00 convert into word as . | Excel Discussion (Misc queries) | |||
| convert decimal number to time : convert 1,59 (minutes, dec) to m | Excel Discussion (Misc queries) | |||
| Convert Time...!convert tenths of a second | Excel Discussion (Misc queries) | |||
Linear Mode
