Populating a single row on template with data from multiple rows on another sheet

[sarzalot]

Device and OS platform: MacBook Pro, macOS Monterey Version 12.5.1

Excel product name and version number: Microsoft Excel for Mac, Version 16.65 (part of a 365 subscription)

Background Info/What the workbook is used for : Our clients send over a census file with the information we need to underwrite and import the eligible employees into our platform.

A few of our clients don't want to, can't, or won't use the template we provide them and instead just export the data and send that file to us regardless of how it's formatted.

Generally, I might just need to change some of the reference columns in a few of my formulas, but this file is a bit trickier and it doesn't look like the formulas I've previously created for other employers can easily be reworked to use on this one.


Excel file you are working with or sample file with data: file-help.xlsx

• Sheet1 is the file we receive from the client (with the actual PPI removed, of course) and I've highlighted the cells that contain the data that is needed for our underwriting template in Sheet2.

• The majority of employees on the file for this client have 7 rows of data, though some will have 5 or possibly even 3 depending on the insurance types they choose to enroll in. In the example provided from rows 30-34, we would want to show 'None' or 'Waived' in column AC on Sheet2 since they are not enrolled in the health insurance.

Hoping to brainstorm with anyone who has suggestions, ideas, advice, etc!

Expected result and method to achieve the result: Coming up with a process that uses formulas to automatically take the data and plug it into the templates we use for our processes.

The fewer manual steps that are involved, the better! We will be getting a new file each week and don't really have the time to do things manually

Note: I posted this on the Microsoft Community Excel Forum yesterday (post there has the same title, I would link to it here but I am not allowed to yet since this is only my first post). Cross-posting here to hopefully get a few more eyes on it!

[JeteMc]

Hello sarzalot and Welcome to Excel Forum.
Try posting the link without the https:// prefix.
Let us know if you have any questions.