Auto-retrieve and manage data from another spreadsheet

Hi

I use Excel 2002.

Spreadsheet 1 : Data source
Column One : 1st Basketball Team
Rockets
Kings

Column Two : 2nd Basketball Team
Orlando Magic
Bulls

Column Three : Score
97:98
87:79

They means (1st row) Rockets vs Orlando Magic (97:98), (2nd row) Kings vs Bulls (87:79)

How can I create the following spreadsheets using VBA?

---------------------------------------------------------------------
Spreadsheet 2 : Auto-retrieve data from Spreadsheet 1

Get basketball team data from column one and column two of spreadsheet 1, put them into one column in spreadsheet 2.

Column one (Sorted by alphabetic order and non-duplicated)
Bulls
Kings
Rockets
Orlando Magic

----------------------------------------------------------------------
Spreadsheet 3 : Auto-retrieve data from spreadsheet 1

Get score data from column three of spreadsheet 1

Split the data 97:98 into 97 and 98 of spreadsheet 1, put them into 2 columns of spreadsheet 2

Column one
97
87

Column two
98
79

-----------------------------------------------------------------------
Spreadsheet 4 : Auto-create a table according to the number of teams. indicate a color of cell according to the score result.

For example, there are 4 teams, the total numbers of round is 2.

Rockets vs Orlando Magic 97:98 round #1
Kings vs Bulls 87:79 round #1

Rockets vs Bulls 90:90 round #2
Kings vs Orlando Magic 91:89 round #2

Column 1 Column 2
Team name Round #1
Rockets Cell changes to Blue color if Rockets lose (97:98)
Orlando Magic Cell changes to Green color if OM won (97:98)
Kings Cell changes to Green color if Kings won (87:79)
Bulls Cell changes to Blue color if Bulls loss (87:79)

Column 1 Column 3
Team name Round #2
Rockets Cell changes to White color if Rockets equals (90:90)
Bulls Cell changes to White color if Bulls equals (90:90)
Kings Cell changes to Green color if Kings won (91:89)
Orlando Magic Cell changes to Green color if OM won (91:89)

Thanks for advice




Please post Excel questions, in the Excel forum.

Thank you.

Hello techissue2008 and welcome to the forum! :)

Please be sure to read the posting guidelines (http://www.xtremevbtalk.com/faq.php?faq=evbf_faq#faq_evbf_rules).


How can I create the following spreadsheets using VBA?
Spreadsheet 2 : Auto-retrieve data from Spreadsheet 1

Get basketball team data from column one and column two of spreadsheet 1, put them into one column in spreadsheet 2.

Column one (Sorted by alphabetic order and non-duplicated)
Bulls
Kings
Rockets
Orlando Magic

With VBA:

To get unqiue values into spreadsheet2 use the range object's advancedfilter method.
To sort the unqiue values within spreadsheet2 use the range object's sort method.

Without VBA:

Or, of course, you could do the above manually, thereby avoiding the use of VBA.



Spreadsheet 3 : Auto-retrieve data from spreadsheet 1
Get score data from column three of spreadsheet 1

Split the data 97:98 into 97 and 98 of spreadsheet 1, put them into 2 columns of spreadsheet 2

Column one
97
87

Column two
98
79

There are so many ways you could so this... here are a couple of possibilities:
With VBA:

You could use the SPLIT function
You could assign the values over to your target sheet and use the range object's TextToColumns method to parse it.

Without VBA:
You could just use formulas in your worksheet (LEFT/RIGHT/FIND functions).


Spreadsheet 4 : Auto-create a table according to the number of teams. indicate a color of cell according to the score result.

For example, there are 4 teams, the total numbers of round is 2.
Rockets vs Orlando Magic 97:98 round #1
Kings vs Bulls 87:79 round #1

Rockets vs Bulls 90:90 round #2
Kings vs Orlando Magic 91:89 round #2

Column 1 Column 2
Team name Round #1
Rockets Cell changes to Blue color if Rockets lose (97:98)
Orlando Magic Cell changes to Green color if OM won (97:98)
Kings Cell changes to Green color if Kings won (87:79)
Bulls Cell changes to Blue color if Bulls loss (87:79)

Column 1 Column 3
Team name Round #2
Rockets Cell changes to White color if Rockets equals (90:90)
Bulls Cell changes to White color if Bulls equals (90:90)
Kings Cell changes to Green color if Kings won (91:89)
Orlando Magic Cell changes to Green color if OM won (91:89)

Thanks for advice
Use conditional formatting.

Colin