•2 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community
Consolidating multiple rows into a column
Our take
If you're grappling with a benefit enrollment data set that features multiple rows for each employee, you're not alone. Transforming this data into a clear and searchable census report can feel daunting, especially when dealing with variable row counts. While pivot tables and VLOOKUPs are helpful, they often fall short in consolidating all relevant enrollment information.
I have a data set that is benefit enrollment data - there is a row for every plan that an employee enrolls in - which means there are variable amounts of rows for every employee.
I need to format this for our call center int a census report where the info is in columns so they can search and see the employee information and the plans enrolled is in columns.
I initially created a pivot table that uses the unique id for each employee and created a vlookup that pulls all the information, but it’s just pulling the first row.
I thought about an if formula for each plan name and then concate to put it all in one column for each employee, but the issue still stands that the amount of rows varies and if I drag it down it’s not necessarily tied to the employee but whatever is in that column.
I feel like I’m so close but so far away.
I know I could pivot columns in PBI but I don’t know that the call center can work with a pbi file. They need to be able to search/filter which is why I thought the reference table would work.
This will have to be done monthly and I’m hoping to find a solution that minimizes the time spent formatting.
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- Formula to re-arrange data set to be able to create a pivot table.I am looking for a formula to re-arrange the data I have: https://preview.redd.it/vkdes6wyxqrg1.png?width=345&format=png&auto=webp&s=a30397f809384603a701af85ad807c46b9bdacf4 This is the format I want: https://preview.redd.it/43zatlm0yqrg1.png?width=450&format=png&auto=webp&s=902ea4ac22cb499f567009801ba6def7766b929e I need to do this the pay data of approx. 100 employees, each employee has a different set categories so the number of times the name needs to repeat will vary. I want to do this so I can create a pivot table, any suggestions are welcome. Using Excel365 Desktop - Beginner to intermediate level knowledge Thank you in advance. submitted by /u/Ok_Chicken_590 [link] [comments]
- Creating Pivot Table from Multiple SheetsHi All, I'm working on a large tracking workbook, consisting of several clinical trials in order to track by patient detail the payments we are owed by the funder, what we have received, and the difference. All these payments are delayed by 3m-2 years in some cases and leadership wants to accurately predict how much we are owed. I think what where I'm running into issues is that while I did standardized as much as I could, there are still several columns for each study that don't apply to other studies. I.e. some studies have different arms they could be enrolled in, some are just a 1 time enrollment payment, others have several milestones that can receive payments. But every sheet has roll ups that are standardized that I need in the Pivot Table. Those being: Protocol Randomized Date Federal Accrued Foundation Accrued Industry Accrued Supplement Accrued Federal Received Foundation Received Industry Received Supplement Received Total Owed The Accrued and received columns sum the individual payments into those buckets, that way we can go back to the funder and ask specifically what we are missing for to see if they missed paying us for that milestone specifically. When I tried pulling all these sheets into Power Query, I was able too, and aggregated all the sheets into one via Power Query. Then I tried to pull that aggregate into a pivot table. No Pivot table Loaded and all I got was "load to data model failed" on each queries. Am I asking for too much? Can I get rid of the extra columns in Power Query that do no align together with ruining the data that is being pulled in by formulas. I have if statements pulling into the table for the individual, study specific milestones, from a separate table that automatically helps us track payments accrued, and the "standard columns" have sums formulas that sum the columns that apply to them from the individual milestone columns. The milestone, study specific received columns are entered in manually and have no formulas, but are rolled up into the standard columns just like in the accrued side. And the total owed column is also a formula of the standard accrued and received columns. The goal of pulling this into a pivot table is so we can give high level data to leadership to actually start tracking how much we are owed, given the constant delay in payments, and to have a real sense of the deficit this specific program runs year to year. This way they can accurately plan for the yearly "donation" from other sources of funding in the department. If you made it through this post, thank you! Any help is appreciated. I'm using Excel 365. submitted by /u/Melodic-Pollution-91 [link] [comments]
- Pivot Calculated Field issueHi guys, Im still learning excel and im having a hard time inserting a formula in a pivot table that initially created in a manual table. I wanted to to utilize the pivot function of opening a new sheet with the filtered values + the formulas that I want to show. Here's a link to the sample data set that i'm using. Here's the breakdown: have 200 transactions over a 3 day period (no dates for this one sadly) with a sudden decline in approval rate divided it into 2 groups (first half, last half) Wanted to make a pivot table checking the approval rates of each of the following: Card type, Country, MID (merchant ID). This is just to investigate the root cause. I already have a sample formula for the Card type part but having trouble inserting it in a pivot table. It should be easy to apply to other pivot tables once I get this one done =(COUNTIFS(Raw!H:H,"First 100",Raw!B:B,"Approved",Raw!D:D,"Amex")/COUNTIFS(Raw!H:H,"First 100",Raw!D:D,"Amex") submitted by /u/NierraSol [link] [comments]
- How do I Maximize File EfficiencyI work with data sets that I typically look at forecast by year. Currently when I look at 2026 and 2027 it is rougly 1.4M lines of data. I have to put these in two separate data pulls and tables. Then I have six different customers included in this data. so I have to create 6 tabs with six diffrent pivot tables for them to look at. This has created a massive file that lags just to open, save or close so I really have two questions and am open to suggestions. Would it be better to store the data in one worksheet and then link a second worksheet that just has the pivot tables and separated look? If so how would I creat that link? Can you explain to me like I am 5 how I would use power query to combine the 26 and 27 table so that they could be in the same pivot table? Every column in both are identical. submitted by /u/dcal69 [link] [comments]