Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 08-13-2021, 06:35 AM
herodot herodot is offline What formula can I use to get the information Windows 10 What formula can I use to get the information Office 2016
Novice
What formula can I use to get the information
 
Join Date: Jun 2020
Posts: 10
herodot is on a distinguished road
Default What formula can I use to get the information

Hello!




Regarding the attached picture, how can I gather the information (using a formula) in my table from the 1st OR the 2nd situation. Doesn't matter which one (if 1 or 2).


I wouldn't like to not complicate myself with VBA.
Attached Images
File Type: png 2021-08-13_161730.png (20.9 KB, 26 views)
Reply With Quote
  #2  
Old 08-13-2021, 02:35 PM
Purfleet Purfleet is offline What formula can I use to get the information Windows 10 What formula can I use to get the information Office 2019
Expert
 
Join Date: Jun 2020
Location: Essex
Posts: 345
Purfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to behold
Default

Please provide an example worksheet so we dont have to waste time recreating data you already have
Reply With Quote
  #3  
Old 08-13-2021, 11:57 PM
herodot herodot is offline What formula can I use to get the information Windows 10 What formula can I use to get the information Office 2016
Novice
What formula can I use to get the information
 
Join Date: Jun 2020
Posts: 10
herodot is on a distinguished road
Default

Of course.



I don't know why I didn't thought about this in the 1st place.
Attached Files
File Type: xlsx example.xlsx (10.7 KB, 9 views)
Reply With Quote
  #4  
Old 08-14-2021, 02:47 PM
p45cal's Avatar
p45cal p45cal is offline What formula can I use to get the information Windows 10 What formula can I use to get the information Office 2019
Expert
 
Join Date: Apr 2014
Posts: 863
p45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant future
Default

Quote:
Originally Posted by herodot View Post
I wouldn't like to not complicate myself with VBA.
Well! I wouldn't like to complicate myself with formulae; for they would be complex indeed.
In the attached are a few Get & transform Data (from that section of the Data tab of the Ribbon) solutions.
Update your Data Tables, then right-click on the green tables and chose Refresh.
The blue tables beneath the green tables are plain pivot tables based on similar queries; they have the added benefit of being able to add grand totals easily as I've done in the right hand blue pivot table.

The results depend heavily on your data in columns B & L beginning with a space or not to determine Activity or Resource.

So you have your 'no VBA' but I suspect you won't like these solutions.
Attached Files
File Type: xlsx msofficeforums47480example.xlsx (31.3 KB, 10 views)
Reply With Quote
  #5  
Old 08-14-2021, 08:11 PM
Purfleet Purfleet is offline What formula can I use to get the information Windows 10 What formula can I use to get the information Office 2019
Expert
 
Join Date: Jun 2020
Location: Essex
Posts: 345
Purfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to behold
Default

I have managed to do with a couple of formula's but it needs helper columns and as p45cal has stated it is all based on spaces (which is rubbish)

Also it is an array formula so you will need to press ctrl+shift+enter
Attached Files
File Type: xlsx Copy of example_AR12.xlsx (12.1 KB, 8 views)
Reply With Quote
  #6  
Old 08-14-2021, 09:11 PM
herodot herodot is offline What formula can I use to get the information Windows 10 What formula can I use to get the information Office 2016
Novice
What formula can I use to get the information
 
Join Date: Jun 2020
Posts: 10
herodot is on a distinguished road
Default

Thank you both of all for your time.

You helped me a lot!!!



The data is extracted from Microsoft Project which automatically insert spaces before those names.
Reply With Quote
  #7  
Old 08-15-2021, 05:45 AM
p45cal's Avatar
p45cal p45cal is offline What formula can I use to get the information Windows 10 What formula can I use to get the information Office 2019
Expert
 
Join Date: Apr 2014
Posts: 863
p45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant future
Default

Quote:
Originally Posted by herodot View Post
The data is extracted from Microsoft Project which automatically insert spaces before those names.
Be careful, if anyone has started an activity or resource name with a space it could mess up.


If you create a pivot table from the range C12:E27 of Purfleet's file you could save yourself having to write out the column and row headers of your table (filter out the blank workers 'though).
2021-08-15_135005.png
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
What formula can I use to get the information formula to grab information on different sheet, only if meeting certain criteria bex_77 Excel 2 10-19-2016 03:42 AM
What formula can I use to get the information Possible to use an existing vlookup formula to also insert correct info and trigger a SUM formula innkeeper9 Excel 2 09-13-2016 08:59 PM
One formula required to copy information from YTD spreadsheet onto individual monthly spreadsheets. Jo Freeman Excel 4 10-28-2015 08:09 AM
What formula can I use to get the information I want to use formula to auto update cells with information Steve81uk Excel 15 01-09-2015 12:58 PM
What formula can I use to get the information I can see my TOC but no information Becabica Word 14 05-22-2012 08:29 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 01:44 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft