Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 51
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 51
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Highlighting Data Exceptions [email protected] Excel Discussion (Misc queries) 2 February 8th 08 11:12 AM
Losing data in controls (and arrays) during unhandled exceptions. David Excel Programming 4 December 6th 06 08:17 PM
Series with noncontiguous unequal length data? [email protected] Charts and Charting in Excel 3 June 15th 06 06:23 PM
Data Validation w/ exceptions msg box TC Excel Worksheet Functions 1 December 22nd 05 11:00 AM
Finding equation between two sets of noncontiguous data vijaya Excel Programming 0 November 3rd 05 10:36 PM


All times are GMT +1. The time now is 09:35 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"