This is a poor’s man manual on how to export Yahoo! group members list into Excel.
Yahoo! used to have this simple feature right on their page, but it is gone now.
Instead you would have to do this painful semi-archaic way of getting the data.
To make it look easy we divived the whole procedure into 3 simple steps:
Step1. Display all your Yahoo! group members in “Display Name” order |
Step2. Copy into Word and fix data inconsistencies |
Step 3. Copy into Excel and create a link sheet with a proper list |
Step1. Display all your Yahoo! group members in “Display Name” order
That is easy. You need to make sure that all your irregular users that for some reason do not have Display Name are grouped together
Step2. Copy into Word and fix data inconsistencies
When you copy data from Browser into Word, you will notice that all data is justone list with all the fields are in one column. You need to fix users that do not have Display Names. If you have too many user without Display Names, you might need to export them separately.
The result of this exercise is the repetitious blocks of data, where user name appears in rows 2, 7, 12 17. User email address appears repeatedly in rows 3, 8, 13, 18 . . .
Step 3. Copy into Excel and create a link sheet with a proper list
As long as you have a list with regular repetition of information on it, you can use function indirect() to extract this information into orderly columns.
Original sheet with raw data contains information repeating every 5 rows. We will use this pattern to link columns to our Excel row number on a linked sheet. Insert an empty Sheet and create 3 columns headers: Name, Email and Date. Now in each of the columns type this formula (provided below) and adjust for your needs.
For Name column I used formula:
=INDIRECT("Sheet1!A" & (ROW()*5)-3)
For Email address I used formula:
=INDIRECT("Sheet1!A" & (ROW()*5)-2)
For Date column I used formula:
=INDIRECT("Sheet1!A" & (ROW()*5)+1)
I did not use information containing in columns Delivery and Posting.
The only problem remaining is that dates for older members are stored in a weird format, and Excel doesn’t know hot to convert them to regular dates, but I do this next spare moment I would find.