Excel formula or macro for string of numbers

Topic

New Reply

Hello – I have a sheet (before) that has a list of numbers. I need it to look like the after sheet.

The numbers in COL A have a .1 and/or a .2. There are usually a pair of numbers like 5.1 and 5.2. There are some, however, that are not “paired”, like 13.1 – there is no 13.2.

If there is a .1 then I need the COL B number to go in COL C. If the next row has the same number but with a .2 in it, then I need the number in COL B to be put in COL D but in the row above – see the after sheet in cell D5. I also need COL A to only have the whole number with no decimal in it.

Thank you for the help.

Reply | Quote

Replies

Did it with Visual Basic. Click on the Go button and it will calculate, move the numbers, convert to integers and remove duplicate numbers (rows). Very good skill practice. It is not possible to accomplish this in excel alone with formulas as a formula cannot delete rows nor manipulate the cells for which they are contained. Could this worksheet have any practicality in real life? Note: save a copy before you do the calculations so you can have an original if you want to perform them again.

Maud

Reply | Quote