Oct 26 2021 08:42 AM
Oct 26 2021 08:42 AM
I have an insurmountable amount of data that I’m trying to move into a more user-friendly format to then run statistical analyses. I am using Microsoft 365, Excel Version 2102 64-bit on a Windows 10.
I'm looking for a formula to put into each cell across the top row of a new sheet that I can then copy and repeat down the lines because I have other items that I'm going to pull from the original data. If someone can provide me a type of 'template' that I can then swap out the pieces so that I can continue to apply to the other data values that I'm trying to pull into this sheet. I'm hoping to pick out pieces of information to include as I have more than I actually need. For example, I will look for Student 1 Fall 2020 Primary Major, but I don't necessarily want all the information that I have for each person's entries to be automatically displayed as an array.
Do I need some type of nested Xlookup? Do I need a helper column? I am a total novice and in over my head. Any help will be greatly appreciated as the only way I know how to do this is by hand which will take me hours and hours, weeks and weeks.
I'm looking for a formula that will search Student Identifier (from column A of the original data), then the term (from column B of the original data), then the Data Element from column C, and give me the data value from column D and put it into this spreadsheet.
I've included a file to help show what I'm working with. The sheet has two tabs: Tab 1 "Data as is" is a snippet of original data. Tab 2 "data format_end goal" which is what I'm hoping to be my transformed data to be more user friendly for statistical analysis in SPSS.
I thank you all in advance for their time and efforts. I'm in awe of the Excel Gurus collective knowledge and hope someone can share some of that knowledge with me.
Oct 26 2021 09:19 AM
@DMcCauley5082 When you mention "an insurmountable amount of data" that you want distribute over a number of columns with texts and number is crying out for Power Query. Are you familiar with that?
Am not able to send you a demo right now, but I'm sure that someone else will.
Oct 26 2021 09:43 AM
@Riny_van_Eekelen I, unfortunately, am not familiar but will go some Google searching and watch a few videos to see what I can learn about it. Thanks for pointing me in a starting direction!
Oct 26 2021 10:30 AM
@DMcCauley5082 You'll find a good start here:
Oct 26 2021 12:34 PM