Spreadsheet: 32990d1229186995-collating-data-in-several-columns-example-for-excelforum
Sheet: Sheet1

Raw Data received - a report comes in for the quarter that shows information from all teams - unfortunately the way the information comes in, does not show the team if it has a zero
return for the quarter, this is sometimes because the team is renamed, reallocated or is closed down, and sometimes new teams appear. The team names do not follow a pattern
as they do below eg. LetterLetterNumberNumber, they are all infact letters and can be as simple as "CWC, or as long as "NCWNULLH", and team names often include peoples initials so there is
no real way of creating a lookup list of names. As mentioned previously I have on a separate sheet copypasted all Quarters team names into column A (for all quaterst) and then did an alphabetical sort
and manually went through the 2000 or so team names and deleted dupliacte listings, then used this as a look up reference point. Was hoping there was a way of selecting something like the table
below and being able to get excel to identify any unique team names, as in count each team name that appears anywhere in the table only once and then produce a list of them.
Ultmately I can then use the lookup references to do a vlookup from the original data files so it shows if each team made a contribution that month or not across the year. This is essential
as we can then look at when each teams came into effect and how this effected our output as an organisation. I get asked to do this on a regular basis as teams submit reports for different services
and am trying to make this a quicker process.
Team | Q3-2006/6 | Team | Q4-2006/7 | Team | Q1-2007/8 | Team | Q2-2007/8 | Team | Q3-2007/8 | Team | Q4-2007/8 | Team | Q1-2008/9 | Team | Q2-2008/9
AA03 | 300 | AA101 | 170 | AA01 | 95 | AA03 | 24 | BB101 | 242 | AA03 | 134 | AA02 | 267 | AA03 | 144
AA101 | 213 | AA15 | 272 | AA02 | 78 | AA101 | 121 | BB103 | 25 | AA101 | 195 | AA03 | 264 | AA101 | 6
AA15 | 248 | AA28 | 14 | AA03 | 51 | AA28 | 260 | BB105 | 132 | AA15 | 148 | [NAME] | 83 | AA15 | 292
AA28 | 55 | AA45 | 148 | [NAME] | 18 | AA45 | 133 | BB11 | 297 | AA28 | 67 | AA06 | 216 | AA28 | 105
AA45 | 27 | AA56 | 67 | AA07 | 184 | AA56 | 198 | BB31 | 88 | AA45 | 115 | AA08 | 119 | AA45 | 84
AA56 | 183 | AA87 | 40 | AA08 | 18 | AA87 | 30 | BB31 | 2 | AA56 | 154 | AA09 | 100 | AA56 | 5
AA87 | 270 | AA89 | 186 | AA10 | 226 | AA89 | 176 | BB34 | 172 | AA87 | 299 | AA10 | 262 | AA87 | 127
AA89 | 88 | BB01 | 124 | AA101 | 213 | BB02 | 57 | BB36 | 296 | AA89 | 199 | AA101 | 191 | AA89 | 216
BB02 | 298 | BB02 | 49 | AA103 | 51 | BB07 | 99 | BB36 | 125 | BB06 | 211 | AA102 | 12 | BB101 | 155
BB07 | 237 | BB03 | 135 | AA105 | 152 | BB101 | 188 | BB61 | 47 | BB07 | 199 | AA103 | 202 | BB103 | 238
BB101 | 143 | BB07 | 198 | [NAME] | 208 | BB101 | 268 | BB72 | 283 | BB08 | 65 | AA104 | 203 | BB105 | 289
BB101 | 184 | BB08 | 31 | AA108 | 195 | BB105 | 242 | BB72 | 93 | BB101 | 115 | AA105 | 157 | BB11 | 156
BB103 | 187 | BB09 | 79 | AA13 | 238 | BB11 | 208 | BB92 | 214 | [NAME] | 282 | AA106 | 34 | BB31 | 78
BB105 | 211 | BB100 | 300 | AA15 | 266 | BB26 | 14 | BB92 | 137 | BB105 | 282 | [NAME] | 118 | BB34 | 270
BB11 | 192 | BB101 | 78 | AA16 | 45 | BB36 | 157 | CC02 | 286 | [NAME] | 108 | AA109 | 248 | BB36 | 183
BB26 | 164 | [NAME] | 39 | AA17 | 81 | BB45 | 247 | CC05 | 23 | BB109 | 294 | AA13 | 124 | BB61 | 147
BB31 | 116 | BB104 | 289 | AA18 | 80 | BB49 | 113 | CC102 | 45 | BB11 | 137 | AA19 | 128 | BB72 | 59
BB34 | 198 | BB105 | 249 | AA21 | 139 | BB51 | 269 | CC103 | 78 | BB12 | 235 | AA20 | 247 | BB92 | 130
BB36 | 27 | BB106 | 54 | AA22 | 243 | BB57 | 75 | CC104 | 287 | [NAME] | 110 | AA21 | 227 | CC10 | 44
BB45 | 223 | [NAME] | 19 | AA24 | 244 | BB58 | 153 | CC106 | 161 | BB14 | 166 | AA22 | 97 | CC30 | 298
BB49 | 92 | BB11 | 120 | AA25 | 192 | BB63 | 97 | [NAME] | 228 | BB15 | 267 | AA25 | 148 | CC33 | 160
BB51 | 236 | [NAME] | 103 | AA26 | 63 | BB66 | 149 | CC109 | 294 | BB18 | 186 | AA27 | 287 | CC72 | 68
BB57 | 33 | BB14 | 209 | AA27 | 300 | BB72 | 266 | CC12 | 134 | BB19 | 117 | AA28 | 271 | CC90 | 60
BB58 | 57 | BB16 | 33 | AA28 | 256 | BB75 | 175 | CC13 | 253 | BB21 | 31 | AA29 | 252 | DD02 | 84
BB61 | 106 | BB17 | 299 | AA32 | 6 | BB80 | 169 | CC15 | 1 | BB22 | 4 | AA30 | 164 | DD03 | 122
BB63 | 129 | BB18 | 230 | AA33 | 211 | BB81 | 275 | CC16 | 156 | BB23 | 230 | AA31 | 198 | DD04 | 9
BB66 | 294 | BB19 | 59 | AA35 | 49 | BB82 | 36 | CC18 | 193 | BB24 | 126 | AA32 | 274 | DD05 | 260
BB72 | 293 | BB21 | 123 | AA36 | 132 | BB86 | 187 | CC19 | 177 | BB27 | 204 | AA33 | 33 | DD06 | 226
BB75 | 3 | BB22 | 275 | AA39 | 187 | BB92 | 264 | CC20 | 148 | BB29 | 265 | AA34 | 31 | [NAME] | 46
BB80 | 285 | BB23 | 126 | AA41 | 258 | BB95 | 183 | CC21 | 249 | BB30 | 242 | AA40 | 173 | DD08 | 140
BB82 | 198 | BB26 | 129 | AA42 | 61 | BB96 | 234 | CC22 | 276 | BB31 | 97 | AA43 | 101 | DD100 | 260
BB86 | 282 | BB28 | 225 | AA43 | 8 | CC10 | 230 | CC23 | 173 | BB32 | 252 | AA46 | 150 | DD101 | 78
BB92 | 221 | BB31 | 57 | AA44 | 119 | CC28 | 117 | CC24 | 130 | BB33 | 177 | AA48 | 263 | DD104 | 298
BB95 | 115 | BB32 | 223 | AA45 | 125 | CC30 | 6 | CC25 | 114 | BB34 | 167 | AA49 | 214 | DD105 | 262
BB96 | 2 | BB33 | 1 | AA47 | 152 | CC33 | 87 | CC26 | 162 | BB36 | 191 | AA50 | 97 | DD106 | 62
CC01 | 121 | BB34 | 251 | AA48 | 269 | CC34 | 180 | CC27 | 51 | BB37 | 175 | AA51 | 288 | [NAME] | 203
CC02 | 116 | BB35 | 70 | AA51 | 18 | CC36 | 122 | CC29 | 84 | BB38 | 2 | AA52 | 150 | DD108 | 3
CC04 | 48 | BB38 | 249 | AA54 | 203 | CC39 | 41 | CC31 | 8 | [NAME] | 289 | AA58 | 195 | DD11 | 115
CC06 | 47 | BB40 | 244 | AA55 | 300 | CC50 | 228 | CC32 | 250 | BB40 | 104 | AA60 | 202 | DD12 | 282
CC07 | 290 | BB41 | 76 | AA56 | 66 | CC63 | 249 | CC35 | 107 | BB42 | 45 | AA62 | 8 | DD13 | 293
CC09 | 55 | BB42 | 94 | AA57 | 175 | CC72 | 220 | CC38 | 227 | BB43 | 261 | AA65 | 82 | DD14 | 102
CC10 | 270 | BB43 | 101 | AA61 | 24 | CC84 | 197 | CC39 | 297 | BB44 | 184 | AA66 | 209 | DD16 | 114
CC100 | 12 | BB45 | 139 | AA62 | 296 | CC88 | 134 | CC40 | 175 | BB45 | 73 | AA68 | 225 | DD17 | 190
CC102 | 270 | BB48 | 201 | AA63 | 95 | CC90 | 20 | CC42 | 210 | BB46 | 51 | AA70 | 81 | DD18 | 57
CC105 | 168 | BB49 | 98 | AA65 | 221 | DD03 | 105 | CC43 | 197 | BB47 | 108 | AA72 | 138 | DD19 | 9
[NAME] | 91 | BB50 | 226 | AA66 | 253 | DD06 | 228 | CC44 | 233 | BB48 | 130 | AA73 | 109 | DD20 | 282
CC13 | 232 | BB51 | 298 | AA69 | 267 | [NAME] | 76 | CC45 | 3 | BB49 | 177 | AA74 | 186 | DD21 | 190
CC14 | 291 | BB52 | 193 | AA71 | 35 | DD09 | 81 | CC46 | 278 | BB50 | 278 | AA76 | 257 | DD22 | 159
CC17 | 73 | BB53 | 136 | AA73 | 243 | DD100 | 241 | CC47 | 76 | BB51 | 228 | [NAME] | 9 | DD23 | 190
CC18 | 45 | BB54 | 85 | AA75 | 221 | DD103 | 214 | CC51 | 85 | BB52 | 70 | AA78 | 224 | DD27 | 271
CC19 | 250 | BB57 | 250 | AA79 | 160 | DD104 | 246 | CC52 | 201 | BB53 | 32 | AA80 | 208 | DD29 | 52
CC20 | 76 | BB58 | 194 | AA80 | 89 | DD105 | 146 | CC53 | 195 | BB54 | 127 | AA84 | 118 | DD30 | 195
CC21 | 87 | BB59 | 237 | AA81 | 24 | [NAME] | 93 | CC54 | 11 | BB55 | 281 | AA85 | 265 | DD32 | 26
CC23 | 220 | BB60 | 257 | AA83 | 153 | DD108 | 171 | CC55 | 286 | BB57 | 18 | AA86 | 54 | DD33 | 20
CC24 | 118 | BB62 | 41 | AA84 | 249 | DD16 | 145 | CC57 | 129 | BB58 | 22 | AA89 | 213 | DD39 | 296
CC25 | 168 | BB63 | 215 | AA85 | 24 | DD17 | 9 | CC58 | 104 | BB60 | 195 | AA90 | 163 | DD41 | 275
CC26 | 45 | BB64 | 176 | AA86 | 58 | DD18 | 259 | CC60 | 298 | BB61 | 110 | AA92 |  | DD42 | 231
CC27 | 64 | BB65 | 212 | AA87 | 159 | DD19 | 288 | CC61 | 74 | BB62 | 239 | AA93 | 80 | DD44 | 227
CC28 | 192 | BB66 | 21 | AA89 | 153 | DD25 | 142 | CC62 | 142 | BB67 | 241 | AA94 | 158 | DD45 | 89
CC29 | 14 | BB67 | 280 | AA90 | 61 | DD26 | 7 | CC63 | 98 | BB71 | 170 | AA95 | 96 | DD46 | 53
CC30 | 85 | BB69 | 156 | AA94 | 121 | DD28 | 287 | CC65 | 255 | BB72 | 245 | AA98 | 152 | DD47 | 36
CC33 | 84 | BB70 | 38 | AA98 | 198 | DD29 | 190 | CC66 | 218 | BB73 | 18 | AA99 | 122 | DD48 | 98
CC34 | 93 | BB72 | 208 | AA99 | 22 | DD36 | 196 | CC67 | 160 | [NAME] | 282 | BB101 | 224 | DD49 | 38
CC36 | 186 | BB75 | 35 | BB07 | 40 | DD37 | 281 | CC68 | 70 | BB76 | 163 | BB11 | 243 | DD51 | 277
CC38 | 132 | BB76 | 186 | BB101 | 190 | DD42 | 220 | CC69 | 139 | BB77 | 124 | BB26 | 43 | DD52 | 241
CC39 | 299 | BB77 | 158 | BB101 | 290 | DD43 | 253 | CC70 | 1 | BB78 | 12 | BB34 | 297 | DD53 | 113
CC41 | 232 | BB79 | 35 | BB103 | 65 | DD48 | 62 | CC72 | 64 | BB79 | 230 | BB36 | 280 | DD54 | 83
CC44 | 61 | BB80 | 76 | BB105 | 290 | DD50 | 4 | CC73 | 31 | BB80 | 68 | BB45 | 150 | DD55 | 29
CC49 | 92 | BB81 | 213 | BB11 | 121 | DD51 | 113 | CC76 | 55 | BB81 | 66 | BB49 | 212 | DD56 | 77
CC50 | 275 | BB82 | 283 | BB26 | 134 | DD53 | 217 | CC77 | 192 | BB84 | 249 | BB57 | 13 | DD57 | 273
CC52 | 94 | BB83 | 245 | BB31 | 146 | DD54 | 102 | CC78 | 12 | BB85 | 220 | BB58 | 153 | DD60 | 143
CC55 | 10 | BB84 | 44 | BB34 | 150 | DD58 | 264 | CC79 | 172 | BB86 | 23 | BB63 | 277 | DD61 | 131
CC56 | 255 | BB86 | 207 | BB36 | 213 | DD61 | 141 | CC80 | 232 | BB92 | 17 | BB80 | 99 | DD62 | 238
CC58 | 268 | BB88 | 255 | BB45 | 96 | DD63 | 140 | CC84 | 295 | BB94 | 70 | BB86 | 65 | DD65 | 276
CC61 | 291 | BB91 | 252 | BB49 | 9 | DD70 | 207 | [NAME] | 19 | BB96 | 186 | BB92 | 59 | DD67 | 63
CC62 | 112 | BB93 | 48 | BB57 | 22 | DD71 | 53 | CC86 | 233 | BB97 | 72 | BB95 | 76 | DD68 | 238
CC63 | 149 | BB95 | 231 | BB58 | 273 | [NAME] | 187 | CC87 | 132 | BB98 | 160 | BB96 | 258 | DD70 | 297
CC65 | 51 | BB96 | 97 | BB61 | 72 | DD77 | 297 | CC88 | 235 | BB99 | 80 | CC30 | 92 | DD72 | 180
CC66 | 15 | BB97 | 252 | BB63 | 161 | DD78 | 192 | CC89 | 127 | CC10 | 163 | CC33 | 210 | DD73 | 46
CC68 | 161 | CC10 | 72 | BB66 | 272 | DD81 | 264 | CC90 | 124 | CC28 | 221 | CC72 | 290 | DD74 | 203
CC69 | 216 | CC28 | 13 | BB72 | 164 | DD82 | 115 | CC92 | 42 | CC30 | 256 | CC90 | 136 | [NAME] | 50
CC70 | 194 | CC30 | 286 | BB75 | 126 | DD84 | 55 | CC94 | 34 | CC33 | 149 |  |  | DD77 | 174
CC71 | 63 | CC33 | 223 | BB80 | 25 | DD85 | 216 | CC95 | 260 | CC34 | 163 |  |  | DD78 | 163
CC72 | 81 | CC34 | 141 | BB86 | 249 | DD89 | 240 | CC96 | 186 | CC36 | 177 |  |  | DD79 | 50
CC73 | 1 | CC36 | 37 | BB92 | 36 | DD94 | 54 | CC97 | 5 | CC50 | 76 |  |  | DD80 | 281
CC74 | 8 | CC39 | 111 | BB95 | 142 | DD98 | 240 | CC99 | 262 | CC63 | 248 |  |  | DD81 | 204
CC75 | 148 | CC50 | 133 | BB96 | 228 | DD99 | 83 |  |  | CC72 | 181 |  |  | DD82 | 148
CC77 | 180 | CC63 | 150 | CC10 | 272 |  |  |  |  | CC84 | 12 |  |  | [NAME] | 217
CC78 | 204 | CC72 | 293 | CC28 | 130 |  |  |  |  | CC90 | 38 |  |  | DD84 | 23
CC79 | 202 | CC84 | 31 | CC30 | 168 |  |  |  |  |  |  |  |  | DD86 | 176
CC81 | 98 | CC88 | 173 | CC33 | 195 |  |  |  |  |  |  |  |  | DD87 | 206
CC83 | 276 | CC90 | 234 | CC34 | 12 |  |  |  |  |  |  |  |  | DD88 | 291
CC84 | 36 |  |  | CC36 | 209 |  |  |  |  |  |  |  |  | DD92 | 3
[NAME] | 84 |  |  | CC39 | 56 |  |  |  |  |  |  |  |  | DD95 | 289
CC88 | 136 |  |  | CC50 | 9 |  |  |  |  |  |  |  |  | DD96 | 262
CC90 | 224 |  |  | CC63 | 132 |  |  |  |  |  |  |  |  | DD97 | 75
CC92 | 134 |  |  | CC72 | 101 |  |  |  |  |  |  |  |  | DD98 | 0
CC94 | 89 |  |  | CC84 | 103 |  |  |  |  |  |  |  |  | DD99 | 75
CC97 | 91 |  |  | CC88 | 145
 |  |  |  | CC90 | 75