UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Auto Fill Issue, Office 2013    
 
   
chondro
post Apr 19 2017, 06:37 PM
Post#1



Posts: 100
Joined: 31-January 10



I need some help. I've tried using flash fill as well as series fill, but they either don't fill the data properly or can't detect a pattern. I'm trying to fill a large number of rows with data. Something like the following:

1
1
1
2
2
2
2
3
3
4
4
4
4
5
5

Based on that, my rows would look like:

1
(blank)
(blank)
2
(blank)
(blank)
(blank)
3
(blank)
4
(blank)
(blank)
(blank)
5
(blank)

As you can see, there's not a consistent pattern in the number of rows in between each new number. I have the starting number in the first row of each group of rows, and I want all the ones in between to be filled with that number. Basically I'm trying to establish that number as a unique identifier for that group of rows so I can convert the data. I can't do this by hand because there are 4,778 unique identifiers, through 65,530 rows (not including the header row). Is there a way I can automate this process?
Go to the top of the page
 
doctor9
post Apr 20 2017, 09:27 AM
Post#2


UtterAccess Editor
Posts: 17,449
Joined: 29-March 05
From: Wisconsin


chondro,

If you have a column filled with numbers, and you want to remove the duplicates, you could add a "helper column" and use that. Basically, assuming the column of numbers is in column A, you would add this formula to column B, starting at B2:

B2: =IF(A1<>A2,A2,"")

Copy that formula down column B the length of column A's data. Now you should have a column that looks like your desired setup; one occurrence of each number, with no duplicates. This is assuming that the data in column A is sorted, of course.

Hope this helps,

Dennis

--------------------
(;,;) Li'l Cthulu says: Please talk about what you're trying to do, as well as how you're doing it.
Changing your real table name to "Table1" and your real form name to "Form1" in your posts makes it more difficult to understand what's going on, not easier.
Guidelines for Posting Questions
Go to the top of the page
 
chondro
post Apr 20 2017, 11:33 AM
Post#3



Posts: 100
Joined: 31-January 10



I think I explained in reverse with my illustrations what I'm trying to achieve. The second list (with the blanks) is what I have. I'm trying to make it look like the first list. I need those blanks filled in with number that's above them. I think because there's no specific pattern to the number of blank rows, that's probably why flash fill won't work.
Go to the top of the page
 
Daniel_Stokley
post Apr 20 2017, 03:02 PM
Post#4



Posts: 227
Joined: 22-December 14
From: Grand Junction, CO, USA


Well, if the data you are starting with has blanks here and there and you want to fill in the blanks, you could use the following formula: IF(ISBLANK(A2),B1,A2)
That formula assumes that your data starts in cell A2. You would put that formula in cell B2 and copy down as necessary.
Here is what it looks like:
Attached File  FillingBlanks.gif ( 5.37K )Number of downloads: 1
Go to the top of the page
 
chondro
post Apr 20 2017, 07:46 PM
Post#5



Posts: 100
Joined: 31-January 10



Worked great! Thank you, you're a life saver!
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    19th August 2017 - 05:18 PM