Now we can go over to the data range area where we want the results to appear, and enter this formula, then copy it across and down: Our first step will be to insert a helper column, and add this formula, then copy it down:Īs you can see, that concatenates the city name from column A with a count of the number of times that city appears in the list. So in this tutorial we are going to show a different way to accomplish the same task. TRANSPOSE populates all the data range, instead of inserting an #N/A for all beyond the first entry.īut Excel populates all four cells with that same value: The one snag that wasn't accounted for was when there was only one entry for a city. If you watched the previous video, you saw how we used the TRANSPOSE function to convert a list that has various multiple entries for each city into a pivoted data range. When you get a preview, look for Download in the upper right hand corner. You can download the file here and follow along. The previous post using TRANSPOSE can be found here. ![]() This option using VLOOKUP eliminates the error. In a previous post I used the TRANSPOSE function, but there was a snag with that concept. In this video we are going to look at an alternate way to take a long data set and transpose the values into a more concise data range.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |