InfoQube IM - Community

Submitted by WayneK on 2015/11/05 18:58

I couldn't find a discussion for adding the "fill series" ability that Excel has.

For example,

Cell 1 = you type in "LM.001.01

Cell 2 = you type in "LM.001.02"

These two entries are enough to establish the rules for the series. In Excel, you then grab the two cells, right click, and drag the selection through the following empty cells. Excel then automatically fills in the correct sequence number for each cell (LM.001.03, LM.001.04 etc).

I'm having to do a lot of manual typing that could be saved with the ability to fill in series. I don't think writing equations would help but I'm open to a suggestion of how it could. Keep in mind that my series definitions are changing constantly, so having to re-do some equation each time is going to be more trouble than it's worth. That's the advantage of Excel's select and drag feature. It can be done quickly without thinking.

Wayne

Comments

Wayne, why not do what you want quickly in excel and paste it into IQ? Or am I not getting something?

Jon

Sony Vaio Z, Intel Core i7, 8 GB RAM, 800 GB SSD

Windows 7 Professional 64 bit

Hi Wayne,

You can use Edit > Renumber to do exactly that !

Copy / Paste from Excel is a good way too. Synergy between available tools...

Thanks for the suggestions but I'm having trouble getting them to work.

1) When I paste a column of numbers from Excel, it does not give me the usual option to paste each line into a separate item. Instead, it pastes all the numbers into a single cell.

2) I didn't know about Edit>renumber. I'll spend some more time with it but right now I'm having problems making it work.

My sequence numbers are a combination of text and numbers. These will sort correctly, as long as I include the extra zero's needed to make the alphabetical sort work.

For example, I cannot the numbering scheme "LR.001.1" etc because I'll get sorts like this:

LR.001.1

LR.001.10

LR.001.2

To avoid that, I need to number them: LR.001.01 etc. I haven't been able to create this system using Edit>renumber.

I've tried: Prefix "LR.001.0", start with 1, step 1.

This gives me LR.001.01, LR.001.02, etc. So far so good, except when it gets to "10" instead of producing

"LR.001.10", it produces "LR.001.010", which leads back to sorting problems.

I guess I'll just need to re-do the formula at that point. The renumber tool would be more convenient if it remembered your last formula instead of forcing you to build it from scratch each time.

I still think the drag/complete series feature of Excel is handy. I use it a lot.

Wayne

Win 7, 64 bit Cyberpower Model 1, C Series (2011) 16 GB RAM

27" Samsung S27A350H, 24" ASUS VW242H 1920x1080

The copy from Excel to IQ should work but it does not. If you paste it into notepad and then into IQ, then it works correctly. I'll fix it in v61. I'll also add support for filling numbers with zeros

You can also achieve it using formatting codes. Create a numeric field and set the format (dropdown just right of the number type) to "LR.001."00

You can then use Edit > Renumber to fill the cells with 1,2,3, ... These will be displayed as correctly. Keep in mind that this method displays it correctly, but the underlying value is numeric (1,2,3,...) It may not be what you're after

Otherwise, these are good suggestions Wayne. I'll do my best to implement them !

Pierre_Admin

[Edit] The reason why the Excel paste does not work is that Excel fills the clipboard with many formats, one of which is an image of the selection and IQ uses that before looking for the text version. I'll change the order, it should do it. [/edit] [Edit2] Fixed in v60a

OK, good deal.

Wayne

Win 7, 64 bit Cyberpower Model 1, C Series (2011) 16 GB RAM

27" Samsung S27A350H, 24" ASUS VW242H 1920x1080

I'm updating v60 to v60a which includes some minor fixes and enhanced renumbering:

Remembers previous values for Prefix, Start and Step unless better ones can be found from the selected text
Supports ? wildcards. So for example, LM.001.??? will give LM.001.001 LM.001.002 LM.001.003, ...

HTH !

Pierre_Admin

[quote=Pierre_Admin]

I'm updating v60 to v60a which includes some minor fixes and enhanced renumbering:

Remembers previous values for Prefix, Start and Step unless better ones can be found from the selected text
Supports ? wildcards. So for example, LM.001.??? will give LM.001.001 LM.001.002 LM.001.003, ...

HTH !

Pierre_Admin

[/quote]

I use the renumbering command all the time. It's very useful.

One danger of that command though is that undo doesn't work for the whole renumbering series. So if you inadvertently renumber the wrong column (e.g. the item column), you could loose a lot of data and not be able to recover it unless you have a recent backup, etc. Ideally, this should be "fixed" -- meant to post about this since a long time (maybe I did but can't remember...).

Thanks!

-------------------------------------------------------

Windows 8.1

Sony Vaio S Series 13 (SVS131E21L)

Ram:8gb, CPU: Intel i5-3230M, 2.6ghz

[quote=Armando]

[/quote]

Fixed in v61 !

Thanks for reporting that issue

Thanks!

-------------------------------------------------------

Windows 8.1

Sony Vaio S Series 13 (SVS131E21L)

Ram:8gb, CPU: Intel i5-3230M, 2.6ghz

I just now tried it and after a little experimentation I figured it out. Thanks for that improvement.

To clarify for others, using the example sequence from above, the dialogue would look like this:

Wayne

Win 7, 64 bit Cyberpower Model 1, C Series (2011) 16 GB RAM

27" Samsung S27A350H, 24" ASUS VW242H 1920x1080

Update: this is working extremely well and is saving me a ton of typing time. I simply select a long list of cells, bring up the dialogue, change one sequence number, and hit return. I don't know how it could be simpler or better designed. I really appreciate the thought that went into this and the effort to get it done quickly

Wayne

Win 7, 64 bit Cyberpower Model 1, C Series (2011) 16 GB RAM

27" Samsung S27A350H, 24" ASUS VW242H 1920x1080

Hi Wayne,

This is great news !!