Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sorting noncontiguous data with exceptions
I have imported vcf files into excel 2003 and the column looks like
this: BEGIN:VCARD VERSION:3.0 N: (some data) FN: (some data) MN: (some data) X-VOIP-USERNAME: (some data) X-VOIP-PSTNNUMBER:(some data) X-VOIP-DISPLAYNAME:(some data) X-VOIP-SEX:(some data) X-VOIP-COUNTRY:(some data) BDAY:(some data) ADR:(some data) TEL;CELL:(some data) END:VCARD BEGIN:VCARD VERSION:3.0 N: (some data) FN: (some data) LN: (some data) X-VOIP-USERNAME: (some data) X-VOIP-PSTNNUMBER:(some data) X-VOIP-DISPLAYNAME:(some data) X-VOIP-SEX:(some data) X-VOIP-COUNTRY:(some data) ADR:(some data) TEL;CELL:(some data) END:VCARD Each person's data is bounded by the BEGIN:VCARD and the END:VCARD. I want to turn this into a spreadsheet with all the categories (e.g. FN, LN, N, ADR, etc) as columns. A simple text-to-columns operation won't work, as some the entries don't contain all the data (for example, some have FN, MN,LN but others have only FN and LN) and if transposed to columns, the info won't match up across the rows. I've made some headway by highlighting a certain category (e.g. CITY -- there were 37 out of 879 out of 879 begin--end groups, then changing an all unneeded category like COUNTRY to CITY:00, then manually deleting 37 extraneous CITY:00 from the files which originally had had a CITY category. Time consuming to say the least, and I've discovered there is a category I wish to keep, but it shows up in 175 of the files --- there's got to a better way. If anyone has any suggestions, I'd be very grateful. Thank you |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sorting noncontiguous data with exceptions
Il 21/11/2012 22:29, al drah ha scritto:
I have imported vcf files into excel 2003 and the column looks like this: BEGIN:VCARD VERSION:3.0 N: (some data) FN: (some data) MN: (some data) X-VOIP-USERNAME: (some data) X-VOIP-PSTNNUMBER:(some data) X-VOIP-DISPLAYNAME:(some data) X-VOIP-SEX:(some data) X-VOIP-COUNTRY:(some data) BDAY:(some data) ADR:(some data) TEL;CELL:(some data) END:VCARD BEGIN:VCARD VERSION:3.0 N: (some data) FN: (some data) LN: (some data) X-VOIP-USERNAME: (some data) X-VOIP-PSTNNUMBER:(some data) X-VOIP-DISPLAYNAME:(some data) X-VOIP-SEX:(some data) X-VOIP-COUNTRY:(some data) ADR:(some data) TEL;CELL:(some data) END:VCARD Each person's data is bounded by the BEGIN:VCARD and the END:VCARD. I want to turn this into a spreadsheet with all the categories (e.g. FN, LN, N, ADR, etc) as columns. A simple text-to-columns operation won't work, as some the entries don't contain all the data (for example, some have FN, MN,LN but others have only FN and LN) and if transposed to columns, the info won't match up across the rows. I've made some headway by highlighting a certain category (e.g. CITY -- there were 37 out of 879 out of 879 begin--end groups, then changing an all unneeded category like COUNTRY to CITY:00, then manually deleting 37 extraneous CITY:00 from the files which originally had had a CITY category. Time consuming to say the least, and I've discovered there is a category I wish to keep, but it shows up in 175 of the files --- there's got to a better way. If anyone has any suggestions, I'd be very grateful. Thank you Your data in column A F1 VERSION G1 N H1 FN I1 LN J1 MN K1 X-VOIP-USERNAME L1 X-VOIP-PSTNNUMBER M1 X-VOIP-DISPLAYNAME N1 X-VOIP-SEX O1 X-VOIP-COUNTRY P1 BDAY Q1 ADR R1 TEL;CELL Put cells as follow: B2 ="A"&D1+1&":A"&65000 C2 =MATCH("BEGIN:VCARD",INDIRECT($B2),)+D1 D2 =MATCH("END:VCARD",INDIRECT($B2),)+D1 E2 ="A"&C2&":A"&D2 F2 =IF(ISNUMBER(MATCH(F$1&"*",INDIRECT($E2),)),SUBSTI TUTE(INDEX(INDIRECT($E2),MATCH(F$1&"*",INDIRECT($E 2),)),F$1&":",""),"") copy F2 in G2:R2 copy B2:R2 down as necessary. Hi, E. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sorting noncontiguous data with exceptions
On Nov 21, 6:20*pm, plinius wrote:
Il 21/11/2012 22:29, al drah ha scritto: I have imported *vcf files into excel 2003 and the column looks like this: BEGIN:VCARD VERSION:3.0 N: (some data) FN: (some data) MN: (some data) X-VOIP-USERNAME: (some data) X-VOIP-PSTNNUMBER:(some data) X-VOIP-DISPLAYNAME:(some data) X-VOIP-SEX:(some data) X-VOIP-COUNTRY:(some data) BDAY:(some data) ADR:(some data) TEL;CELL:(some data) END:VCARD BEGIN:VCARD VERSION:3.0 N: (some data) FN: (some data) LN: (some data) X-VOIP-USERNAME: (some data) X-VOIP-PSTNNUMBER:(some data) X-VOIP-DISPLAYNAME:(some data) X-VOIP-SEX:(some data) X-VOIP-COUNTRY:(some data) ADR:(some data) TEL;CELL:(some data) END:VCARD Each person's data is bounded by the BEGIN:VCARD and the END:VCARD. I want to turn this into a spreadsheet with all the categories (e.g. FN, LN, N, ADR, etc) as columns. A simple text-to-columns operation won't work, as some the entries don't contain all the data (for example, some have FN, MN,LN but others have only FN and LN) and if transposed to columns, the info won't match up across the rows. I've made some headway by highlighting a certain category (e.g. CITY -- there were 37 out of 879 out of 879 begin--end groups, *then changing an all unneeded category like COUNTRY to CITY:00, then manually deleting 37 extraneous CITY:00 from the files which originally had had a CITY category. *Time consuming to say the least, and I've discovered there is a category I wish to keep, but it shows up in 175 of the files --- there's got to a better way. If anyone has any suggestions, I'd be very grateful. Thank you Your data in column A F1 * * *VERSION G1 * * *N H1 * * *FN I1 * * *LN J1 * * *MN K1 * * *X-VOIP-USERNAME L1 * * *X-VOIP-PSTNNUMBER M1 * * *X-VOIP-DISPLAYNAME N1 * * *X-VOIP-SEX O1 * * *X-VOIP-COUNTRY P1 * * *BDAY Q1 * * *ADR R1 * * *TEL;CELL Put cells as follow: B2 * * *="A"&D1+1&":A"&65000 C2 * * *=MATCH("BEGIN:VCARD",INDIRECT($B2),)+D1 D2 * * *=MATCH("END:VCARD",INDIRECT($B2),)+D1 E2 * * *="A"&C2&":A"&D2 F2 =IF(ISNUMBER(MATCH(F$1&"*",INDIRECT($E2),)),SUBSTI TUTE(INDEX(INDIRECT($E2),MATCH(F$1&"*",INDIRECT($E 2),)),F$1&":",""),"") copy F2 in G2:R2 copy B2:R2 down as necessary. Hi, E. Signore Plinius --- MOLTO GRAZIE! !!! That worked perfectly! Thank you SO much for your quick and helpful reply. You have saved me hours of work. con molta gratitudine, Al |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sorting noncontiguous data with exceptions
Il 22/11/2012 05:45, al drah ha scritto:
On Nov 21, 6:20 pm, plinius wrote: Il 21/11/2012 22:29, al drah ha scritto: I have imported vcf files into excel 2003 and the column looks like this: BEGIN:VCARD VERSION:3.0 N: (some data) FN: (some data) MN: (some data) X-VOIP-USERNAME: (some data) X-VOIP-PSTNNUMBER:(some data) X-VOIP-DISPLAYNAME:(some data) X-VOIP-SEX:(some data) X-VOIP-COUNTRY:(some data) BDAY:(some data) ADR:(some data) TEL;CELL:(some data) END:VCARD BEGIN:VCARD VERSION:3.0 N: (some data) FN: (some data) LN: (some data) X-VOIP-USERNAME: (some data) X-VOIP-PSTNNUMBER:(some data) X-VOIP-DISPLAYNAME:(some data) X-VOIP-SEX:(some data) X-VOIP-COUNTRY:(some data) ADR:(some data) TEL;CELL:(some data) END:VCARD Each person's data is bounded by the BEGIN:VCARD and the END:VCARD. I want to turn this into a spreadsheet with all the categories (e.g. FN, LN, N, ADR, etc) as columns. A simple text-to-columns operation won't work, as some the entries don't contain all the data (for example, some have FN, MN,LN but others have only FN and LN) and if transposed to columns, the info won't match up across the rows. I've made some headway by highlighting a certain category (e.g. CITY -- there were 37 out of 879 out of 879 begin--end groups, then changing an all unneeded category like COUNTRY to CITY:00, then manually deleting 37 extraneous CITY:00 from the files which originally had had a CITY category. Time consuming to say the least, and I've discovered there is a category I wish to keep, but it shows up in 175 of the files --- there's got to a better way. If anyone has any suggestions, I'd be very grateful. Thank you Your data in column A F1 VERSION G1 N H1 FN I1 LN J1 MN K1 X-VOIP-USERNAME L1 X-VOIP-PSTNNUMBER M1 X-VOIP-DISPLAYNAME N1 X-VOIP-SEX O1 X-VOIP-COUNTRY P1 BDAY Q1 ADR R1 TEL;CELL Put cells as follow: B2 ="A"&D1+1&":A"&65000 C2 =MATCH("BEGIN:VCARD",INDIRECT($B2),)+D1 D2 =MATCH("END:VCARD",INDIRECT($B2),)+D1 E2 ="A"&C2&":A"&D2 F2 =IF(ISNUMBER(MATCH(F$1&"*",INDIRECT($E2),)),SUBSTI TUTE(INDEX(INDIRECT($E2),MATCH(F$1&"*",INDIRECT($E 2),)),F$1&":",""),"") copy F2 in G2:R2 copy B2:R2 down as necessary. Hi, E. Signore Plinius --- MOLTO GRAZIE! !!! That worked perfectly! Thank you SO much for your quick and helpful reply. You have saved me hours of work. con molta gratitudine, Al Felice di esserti stato di aiuto :-) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Highlighting Data Exceptions | Excel Discussion (Misc queries) | |||
Losing data in controls (and arrays) during unhandled exceptions. | Excel Programming | |||
Series with noncontiguous unequal length data? | Charts and Charting in Excel | |||
Data Validation w/ exceptions msg box | Excel Worksheet Functions | |||
Finding equation between two sets of noncontiguous data | Excel Programming |