Interesting (Cell Offset?) Problem

[Jtucker10278]

I have data that was exported from Access in a way that is difficult for me to use.
(if it helps to understand I am trying to link panels to a layout or page number, and each layout or page number may have one or more panels)

the data columns are easy

Layout Number, Panel Number, qty

but they are offset in the rows.

What I need is for each panel number to have the layout number for it, in the column to the left.
like this

Now I know I could redo the query in Access but I don't have access to the actual data, just a data dump that was given to me.
and I am sure someone could probably write an vba function to do what I need (which would be appreciated if its possible and someone can)

But really, I thought this was going to be a simple "iftext then equal cell x"
and now after about an hour of fighting with it trying different things I'm currious if there was a simple way to do it with formulas, or even a complex way if it could even be done using formulas alone.

Thanks for the help

 

[Candyman8019]

You can organize this data with formulas. Get the first couple of rows done and then you can copy the formulas down until all of your data is captured. In order for this to work you need a counter column that starts at 1 then 2 and increments by 2 from there. Here is an example:

Book1.xlsm
	A	B	C	D	E	F	G	H
1	wse1-13				1	wse1-13	sp55	4
2		sp55	4		2	wse1-14	sp66	4
3	wse1-14				4	wse1-15	sp67	4
4		sp66	4		6	0	0	0
5	wse1-15				8	0	0	0
6		sp67	4		10	0	0	0
7
8
9
10
11
Sheet3

Cell Formulas
Range	Formula
F1	F1	=+OFFSET(A$1,0,0)
G1	G1	=+OFFSET(A$1,E1,1)
H1	H1	=+OFFSET(A$1,E1,2)
F2:F6	F2	=+OFFSET(A$1,E2,0)
G2:G6	G2	=+OFFSET(A$1,E2+1,1)
H2:H6	H2	=+OFFSET(A$1,E2+1,2)

Once you have your data organized, you can copy|paste values to make sure it is retained.

 

[RoryA]

You could also:
Select the first column data.
f5, Special and select Blank cells.
Type = then press the up arrow key, and press Ctrl+Enter
Copy the column of data and then paste special - values.
Use an autofilter to delete the rows where the second column is blank.

 

[Jtucker10278]

RoryA........

That little trick could have probably saved me a years worth of time on all the different projects I have done playing with excel over the past 25 years.
I am both excited that I now know it and devastated that I didn't know it before.

Thank you so much!

 

[RoryA]

Yep, it has definitely saved me a lot of time!