•1 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community
Can excel find data in a sheet, if that data sometimes may not exist/be in the same cells?
Our take
Navigating data in Excel can be challenging, especially when raw data doesn't consistently align with your final sheet. If you're looking to automatically match employee names to their corresponding data, even when that data appears in different cells, you're in the right place. By using a formula to tell Excel, “if you see Jane Doe, pull the data two columns to the right,” you can streamline your weekly reporting without the hassle of manual data transfer.
I am making a sheet at my work that basically sums up stats about staff each week. However, the internal source I pull the raw data from only shows employees who actually worked on a given day. My final sheet will show daily data and weekly stats.
I am trying to find a way to create a formula that will match that employee's name with their data on the raw data sheet and pull it into the finished sheet. But it will not be in the same cell each time. I am trying to avoid hand-tranferring the data if possible.
What I really want is a formula that will tell it "if you see Jane Doe, put the data two columns to the right of her name into this cell."
Excel LTSC MSO Version 2408
English language, Windows desktop, beginner proficiency
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- Monthly tracking workbook I use to track employee sales metrics; Trying to find a way to make the process less labour intensiveTruly having a hard to describing my issue effectively but hoping someone can help. First time posting here and I'm by no means an expert with excel, so please be kind! I have a monthly workbook where I track each employees revenue and other metrics. Every 2 weeks for payroll, I provide a print out of these numbers, and the payroll sheet pulls data from multiple sheets in the workbook. For example, every workbook has a separate sheet for each day of the month, titled "1" through "31". I have pay period sheets, so I'll use one titled "04.02.26 - 04.15.26". Then I'll have the data for each employee pulled from multiple sheets. For example, I use the formula =SUM('2:15'!E3) to pull the sales data from each day of that period for the specific employee. This works quite well. However, when I create a new month's spreadsheet, I have to manually alter this formula for each employee and for each data point (more than just revenue, at least 6 different data points for 7 employees). Is there a way to automate this? For example, a cell or two where I'm able to enter the date range and all of the formulas update to that date range for the corresponding pages? I'm sorry of this post is confusing. Truly it's confusing even typing it! submitted by /u/Ok_Smile9222 [link] [comments]
- How to autofill data from a row to a column on a different sheet in the same folder?I've been struggling with some solutions I've found on the forum but after 1.5hrs I'm close to giving up and manually entering data - which is bound to cost me another 28 hrs. Hoping someone has the solution I'm looking for and is willing to share.. I've exported questionnaire results from Mentimeter to Excel. The document output is formatted automatically in a way that uses columns for unique respondents, followed by their answers in the same column but along individual cells on that column's row, meaning the first entry is A2 and the last entry is in cell CQ2 or something. I would like to make this more user-friendly by: 1) putting each respondent's answers in their own sheet in the folder, and 2) by listing the questions in the first column and the answers in the next columns pretty much 'the other way around'. Currently it looks like this; the answers I need are listed in !VotersF3 to !VotersCQ3. The next respondent's answers are in !VotersF4 through to !VotersCQ4 and so on. What I'm looking for would ideally display answers in !AnswerA3 through to A80. When I manually select !AnswerA3 and click on !VotersF3, logically it does what I want. When I then drag down to autofill, equally logically the sheet enters !VotersF4 instead of !VotersB3 as it's a row vs column problem. I've tried different version of INDEX and TRANSPOSE but I can't get a working formula from that. Would anyone be able to provide me with the correct solution for doing this? I've got another 20+ respondents answers that need to be 'easy to view' instead of scrolling 500 screens horizontally.... Thank you Excel wizards! :) submitted by /u/Lost_Mud2097 [link] [comments]
- Using Data from Two Sheets to find classesI work in a corporate job and somehow became the person on my team that knows excel best. I’m okay with excel. Not an expert. But I need help. I have a spreadsheet that lists available training classes and how many seats the class has and how many are still available. I also have a sheet of learner requests for classes. I would love to combine the data and be able to query or pivot to show a list of students that could be enrolled into available classes. What’s the best method of going about that? Thanks. If I need to go elsewhere to ask questions like this please direct me. Thanks. submitted by /u/Coffee4words [link] [comments]
- Is there a way to reference an array of arrays?Is there a way to get Excel to use an array of arrays in in a formula? Instead of writing a formula that references an array and dragging it down through a column, can I write the formula in a way that results in an output of SUM(O2#),SUM(O3#),SUM(O4#),SUM(O5#),...,SUM(O1000#)}? I work with enormous data sets, but they vary in size. Since I need the workbooks to work with any amount of data, I end up dragging formulas down extra far, leaving rows that just output empyy values when the data doesn't reach that far. If I can make the formula reference an array of arrays then I could eliminate all those garbage rows. Thank you. Unfortunately, because a security restrictions, I can't use macros or VBA at all. I need to do it everything through standard excel syntax. EDIT: Sorry, I didn't make it clear that I used SUM() as an example to try and simplify and generalize. Here's a more explicit example of what I'm doing: ID Data Col C Col D A 14 =UNIQUE(ID#) =IF($C1="","",LET(ArrSt1,TRIMRANGE(DROP($B:$B,4),2),ArrSt3,TRIMRANGE(DROP($H:$H,4),2),ArrA,(FILTER(ArrSt3,ArrSt1=C2,)),TRANSPOSE(FILTER(ArrA,ArrA<>"")))) A 23 =IF($C2="","",LET(ArrSt1,TRIMRANGE(DROP($B:$B,4),2),ArrSt3,TRIMRANGE(DROP($H:$H,4),2),ArrA,(FILTER(ArrSt3,ArrSt1=C2)),TRANSPOSE(FILTER(ArrA,ArrA<>"")))) A 42 =IF($C3="","",LET(ArrSt1,TRIMRANGE(DROP($B:$B,4),2),ArrSt3,TRIMRANGE(DROP($H:$H,4),2),ArrA,(FILTER(ArrSt3,ArrSt1=C3,)),TRANSPOSE(FILTER(ArrA,ArrA<>"")))) B 1 =IF($C4="","",LET(ArrSt1,TRIMRANGE(DROP($B:$B,4),2),ArrSt3,TRIMRANGE(DROP($H:$H,4),2),ArrA,(FILTER(ArrSt3,ArrSt1=C4,)),TRANSPOSE(FILTER(ArrA,ArrA<>"")))) B 2 =IF($C5="","",LET(ArrSt1,TRIMRANGE(DROP($B:$B,4),2),ArrSt3,TRIMRANGE(DROP($H:$H,4),2),ArrA,(FILTER(ArrSt3,ArrSt1=C5,)),TRANSPOSE(FILTER(ArrA,ArrA<>"")))) B =IF($C6="","",LET(ArrSt1,TRIMRANGE(DROP($B:$B,4),2),ArrSt3,TRIMRANGE(DROP($H:$H,4),2),ArrA,(FILTER(ArrSt3,ArrSt1=C6,)),TRANSPOSE(FILTER(ArrA,ArrA<>"")))) B =IF($C7="","",LET(ArrSt1,TRIMRANGE(DROP($B:$B,4),2),ArrSt3,TRIMRANGE(DROP($H:$H,4),2),ArrA,(FILTER(ArrSt3,ArrSt1=C7,)),TRANSPOSE(FILTER(ArrA,ArrA<>"")))) B 90 =IF($C8="","",LET(ArrSt1,TRIMRANGE(DROP($B:$B,4),2),ArrSt3,TRIMRANGE(DROP($H:$H,4),2),ArrA,(FILTER(ArrSt3,ArrSt1=C8,)),TRANSPOSE(FILTER(ArrA,ArrA<>"")))) B 94 =IF($C9="","",LET(ArrSt1,TRIMRANGE(DROP($B:$B,4),2),ArrSt3,TRIMRANGE(DROP($H:$H,4),2),ArrA,(FILTER(ArrSt3,ArrSt1=C9,)),TRANSPOSE(FILTER(ArrA,ArrA<>"")))) =IF($C10="","",LET(ArrSt1,TRIMRANGE(DROP($B:$B,4),2),ArrSt3,TRIMRANGE(DROP($H:$H,4),2),ArrA,(FILTER(ArrSt3,ArrSt1=C10)),TRANSPOSE(FILTER(ArrA,ArrA<>"")))) ... =IF($C1000="","",LET(ArrSt1,TRIMRANGE(DROP($B:$B,4),2),ArrSt3,TRIMRANGE(DROP($H:$H,4),2),ArrA,(FILTER(ArrSt3,ArrSt1=C1000,)),TRANSPOSE(FILTER(ArrA,ArrA<>"")))) Output: ID Data Col C Col D Col E Col F F A 14 A 14 23 42 A 23 B 1 2 90 94 A 42 "" B 1 "" B 2 "" B "" B "" B 90 "" B 94 "" "" ... "" There are a lot of data manipulations omitted, so if something seems weird (like the empty cells in Data Col) please ignore it. The questions is how to eliminate the unneeded rows in Col D. Thank you. EDIT2: Studying the BYROW, SCAN, and MAP suggestions, led to this page on Exceljet, https://exceljet.net/glossary/array-of-arrays , that suggests my question is a known problem in Excel's engine and there is no work-around. Thank you to everyone who tried to help. submitted by /u/TwitchyDingo [link] [comments]
Tagged with
#Excel alternatives for data analysis#real-time data collaboration#big data management in spreadsheets#generative AI for data analysis#conversational data analysis#intelligent data visualization#Excel compatibility#AI formula generation techniques#rows.com#natural language processing for spreadsheets#financial modeling with spreadsheets#Excel#raw data#formula#data matching#employees#daily data#employee name#data pulling#staff stats