Hello,
I am trying to come up with a filter formula or if formula where it looks at a raw data report out of an ERP system where it scans the column and creates a list based on data that falls between the label Move-Ins and Move-In Total. This raw data would be dropped in each month so the list can grow from one to many that fall in between those two text labels (see photo example).
Movein.PNG
Hi there.
A picture is worth 1,000 words. An Excel sheet is worth 1,000 non-editable pictures.
Please read the yellow banner about sample worksheets, at the top of the screen. Act on its guidelines and post a SMALL sample sheet complete with an explanation and some expected results.
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU
Hi amartin575,
Your problem looks like it needs Power Query for an answer. I assume you have a very long worksheet with many of the data pictured above, or many workbooks with this type of data. You can use Power Query (if you have a newer version of Excel) and filter out all rows except those that end with "AIM System" and then chop it up into columns, if it isn't already.
Do you have Power Query available? Is your profile correct where you have Excel 2013?
One test is worth a thousand opinions.
Click the * Add Reputation below to say thanks.
Example of the file is attached. The section for Move-ins is starting around row 86. I need to do this for another section as well but was hoping once I see how to do it with one I can edit to do for the second on my own. The other thing is it should filter pulling only the names within Move-ins and Move-in Total but exclude the header row that shows Name right below the Move-Ins field.
Last edited by amartin575; 02-13-2023 at 10:53 AM.
Sorry, I do not understand this. Where are the data coming from??
The data is coming from a system and the designer of the report you are seeing has a crazy amount of columns and formatting that is a mess. From the report I attached they need to pull information off of it onto a summary sheet. So using Move-Ins or Move-Outs as an example from the attached I need to pull any names listed in between the Move-ins and Move-in total from that report over to a summary sheet. So trying to figure out if there is a dynamic filter formula or something that will say pull everything in between Move-ins and Move-in total over to a different sheet. This would be a template where they can drop that report in each month and it auto populates the summary.
I attached a new file with the summary tab included. You will see weekly move-outs section. I am trying to auto fill that section from the AMCMondayRptRaw tab which each week may have one to many people on that move out section that needs to fill this grid on the summary.
I can not find Kelley, Hines or Olipendo anywhere in AMCMondayRptRaw!!!!
This does not make sense...
That is old data that was manually input. I am looking to automate that with a filter formula or something. So once a formula is input that grids information would change to what is on the Raw report.
Apologize about that. I should have mentioned it.
Please supply a sample file with expected results... that match the raw data. No more guessing games, please.
Here is the updated file with the expected results on the Summary Tab.
G18. Work in progress.
This is very UNDER-described, especially after 12 posts.
Where do Type, Rental rate and Market Rate come from????????????
It would help if you TOLD us where ALL your expected answers are supposed to come from....
Thank you for your time and effort. I really appreciate it. I didn't provide all of the requirements because I wanted to take what you gave and figure out the rest on my own so that I learn from it. As far as the rental rate, market rate and type it comes from other sections within the report. So my plan was once this grid had information populated I was going to use that information to pull the other fields in from the appropriate section of the report. I never meant to be a Pain In The You Know What with this by not giving all of the details. It is a file I am helping my wife with to automate for her job. As I mentioned before the report writing of the export to csv from the system is horrible as you can see with the slew of columns etc. I should be able to populate the other sections from here. Have a great weekend! And as mentioned I appreciate all of your help. You have been pretty patient and responsive.
If you hit a problem, just come back & tell me where they are to be found, and I can incorporate them!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks