Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 10-17-2023, 05:13 PM
AVaughn AVaughn is offline Robust list "tidying" (iterative/circular functions)? Windows 7 64bit Robust list "tidying" (iterative/circular functions)? Office 2007
Novice
Robust list "tidying" (iterative/circular functions)?
 
Join Date: Jan 2023
Posts: 3
AVaughn is on a distinguished road
Default Robust list "tidying" (iterative/circular functions)?

Hi guys,




I had an idea for a document: there is a worksheet with a table, tons of entries (rows) in this table. However in a second worksheet, certain characteristics are looked up from that big table, and only certain rows are returned.


The document has to do this "live" (real time) without needing to manually press buttons, and the document cannot use macros, plus it has to be compatible with 2007 excel version. So really it must use smart code inside some cells.


The idea is, that anybody could add items onto the end of the big messy table, however in another worksheet, relevant items would continue to be shown there, even when new ones are added.



What I don't have a problem with:
Returning certain rows based on info from a column
Removing duplicate entires (haven't run into this yet)...


What I do have a problem with:
Removing any and all spaces from the "smaller" list that is generated.



As someone with rudimentary coding knowledge, I set out to find a method where this can be done, but man, was it not easy - I learned about iterative/circular functions, where cells can re-calculate and have some self-awareness regarding their own contents, and this worked great for me, I made some test programs and they worked, however they weren't "manipulating cells" they were just maths...



So, I am treating this a bit like stack overflow, I have provided a minimum reproducible example. In the attached document there are two little tables of data, I removed anything that was not necessary, so it's very bare bones. I don't even think these functions support the addition of new data when they are "live" yet, but I am dealing with one problem at a time.


The left column, is the "original data", so to say, so it is not to be touched, and the right column is supposed to be the version with spaces removed.


If you write anything in cell I3 (I for indigo), the cells will begin work, but they will only iterate once - if you press F9 a bunch of times, they will get to the end of what they're doing (move everything to top, without affecting order). My question is, can I do anything to make it automatic, so that I don't need to press F9 a bunch of times? By the way, deleting whatever is inside cell I3 will "reset" everything and stop functions from calculating stuff, it's a little debugging thing.



I decided to write here because maybe there is a better way, obviously the end result of what I want to do will be quite long code, but I don't want people to do all my work for me, I am just stuck on one specific part of things.


Thanks in advance, love you all
Reply With Quote
  #2  
Old 10-17-2023, 05:18 PM
AVaughn AVaughn is offline Robust list "tidying" (iterative/circular functions)? Windows 7 64bit Robust list "tidying" (iterative/circular functions)? Office 2007
Novice
Robust list "tidying" (iterative/circular functions)?
 
Join Date: Jan 2023
Posts: 3
AVaughn is on a distinguished road
Default

What the heck, I attached and uploaded the doc, where did it go??


Sorry guys, here is a Google Drive link to the doc:


Книга5.xlsx - Google Sheets


Incredibly, Google tries to open the file, just do "file>download" at the top, please don't view it in Google sheets, I doubt anything good will come from that.
Reply With Quote
  #3  
Old 10-18-2023, 12:29 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Robust list "tidying" (iterative/circular functions)? Windows 10 Robust list "tidying" (iterative/circular functions)? Office 2021
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,747
Pecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant future
Default

Hi
please post a sheet on the board ( external links are potentially unsafe), use the " go advanced" button in the Quick Reply window
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post
Reply With Quote
  #4  
Old 10-18-2023, 09:24 AM
AVaughn AVaughn is offline Robust list "tidying" (iterative/circular functions)? Windows 7 64bit Robust list "tidying" (iterative/circular functions)? Office 2007
Novice
Robust list "tidying" (iterative/circular functions)?
 
Join Date: Jan 2023
Posts: 3
AVaughn is on a distinguished road
Default

Hi, thanks for the reply. I swear I've been around the block, but it just didn't attach my dang doc, I will try again!

Please work...
Attached Files
File Type: xlsx Книга5.xlsx (10.3 KB, 2 views)
Reply With Quote
  #5  
Old 10-22-2023, 06:57 AM
p45cal's Avatar
p45cal p45cal is online now Robust list "tidying" (iterative/circular functions)? Windows 10 Robust list "tidying" (iterative/circular functions)? Office 2021
Expert
 
Join Date: Apr 2014
Posts: 855
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

Have a look at sheet Лист2, column C.
I don't have Excel 2007, so the formula in cell C3 may have to be entered using Ctrl+Shift+Enter rather than just Enter, then copied down.
For real data, the ranges may have to be extended.
Note also that the ROW()-2 part of the formula contains a 2 because the first cell is in row 3; row 3 - 2 returns 1, the SMALLest index to return. This means, whichever row your topmost formula is in, you need to replace the 2 with the row number of the formula - 1.
Not sure if IFERROR is available in Excel 2007 so the same formula without it in column B.


Anyway, if should give you some ideas.
Attached Files
File Type: xlsx msofficeforums51512Книга5.xlsx (11.9 KB, 4 views)
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
I keep getting messages that there is "one or more circular references" in nearly every spreadsheet MaryannaC Excel 3 02-21-2022 02:39 PM
"Circular References," but formatted as text newmaven Excel 3 12-13-2016 02:23 PM
Robust list "tidying" (iterative/circular functions)? Creating a "super macro" with all kind of functions Xanthopteryx PowerPoint 1 06-16-2016 01:38 AM
Robust list "tidying" (iterative/circular functions)? Need to restore custom functions from a corrupt ".xlam" file ... gamouning Excel 1 11-12-2015 11:03 AM
Robust list "tidying" (iterative/circular functions)? How to choose a "List" for certain "Heading" from "Modify" tool? Jamal NUMAN Word 2 07-03-2011 03:11 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 05:28 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