Simple Excel question

OCdude

OCdude

OCdude

Soldato
Joined
24 Feb 2008
Posts
2,704
Location
Norway
I need to "move" the data from column B into the correct cells in column A (e.g. B1 > A2, B2 > A4).

What's the most logical way of doing this?!

I've tried filtering column A, so that only the corresponding cells show (i.e. the cells with "NAME=" in them) and them copying column B over the top... but that clearly isn't working)

Thanks!

sfxt.png


(All I started off to do, was to add "Chapter x" after each "CHAPTERxxNAME=" cell. I know how to do that using another list, along with concatenate, but i'm not sure how to quickly apply the concatenate function to each alternate cell)
 
This is a little bit more complicated than a 'Simple Excel Question' :)

First, you need to use Text to Columns (under Data) on Column B and delimit by fixed width to separate Chapter01Name= and Chapter1 etc.

Excel1_zps9bd641cd.png


Next, copy columns B and C onto a new chart (makes it easier this way). Now under Column D type the following formula:-

=VLOOKUP(A1,[Book2]Sheet1!$1:$1048576,2,0)

This assumes that your new worksheet is called 'Book2'.

Now using the fill handle, drag your formula and the correct values should appear.

Excel2_zps04b6a197.png


You can now paste values and remove the '#N/A' values.

Finally, concatenate column A with column D, remembering again to paste values.

Excel3_zps27571c4e.png
 
Last edited:
Are you trying to make this too complicated?

Something like this should get you started.

Keep your column A data.

In Column B you want to 'extract' whether your current row needs to be left unchanged (eg 1,3,5,7,9,etc), or if it needs something done to it.

One way to do this would be:

=IF(RIGHT(A1,1)="=", MID(A1,8,2), "")

This basically says if the data in column A ends with an '=' sign, then calculate the chapter number in the format xx (eg 01). Otherwise do nothing.

Then in Column C have something like:

=IF(B1<>"",CONCATENATE(A1&"Chapter "&B1),A1)

This basically says, if data in Column B is blank, then just leave the A data untouched (eg CHAPTER01=00:00:00.000), but if B has data in it (eg 01), then use this to generate a longer string.


This solution would give the output in to form 'CHAPTER01NAME=Chapter 01' instead of 'CHAPTER01NAME=Chapter 1', but that is easily fixed by adjusting the formula in Column B.
 
If you can be sure that your data is always exactly as shown (ie 2 rows per 'index') then an easier solution is:

Original data in column A

In Column B:

=IF(RIGHT(A2,1)="=", CONCATENATE(A2&"Chapter"&ROW()/2),A2)

This basically means that all odd rows are untouched and all even rows (the ones ending in '=') get the right information added to them.
 
Yeah a lot will depend on his actual data and how much it varies.

I suspect this may well be what he wants, as long as his data is all in the format shown:

=IF(RIGHT(A2,1)="=", CONCATENATE(A2&"Chapter"&ROW()/2),A2)
 
I feel a lot dumber now - THANKS A LOT GUYS :(

Thanks for the input though! :D

(don't hate me, but I ended up doing it manually in notepad in the end - lol)
 
You must log in or register to reply here.
Back
Top Bottom