Copy cell value within a row range in a single column, if cell is not empty

[alex_kos92]

Hello

I have columns H-S and some cells have a value, others are empty.
1 row contains only values in column H2 and I2 , while J2-S2 are empty. 2. row contains values from H3-O3, P3-S3 are empty.
I want values from H2 and I2 to be copied to AD2 and AD3 and H3 to be copied to AD4 and so on. So I need a macro for looking up for values within H-S columns and copy in column AD only if cell has a value, otherwise look up in the next row.

Can someone please help?

Thank you

 

[Peter_SSs]

Welcome to the MrExcel board!

We may understand better if you give us some sample data and expected results that we can test with. The following may help:
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[alex_kos92]

Hello

I have columns H-S and some cells have a value, others are empty.
1 row contains only values in column H2 and I2 , while J2-S2 are empty. 2. row contains values from H3-O3, P3-S3 are empty.
I want values from H2 and I2 to be copied to AD2 and AD3 and H3 to be copied to AD4 and so on. So I need a macro for looking up for values within H-S columns and copy in column AD only if cell has a value, otherwise look up in the next row.

Can someone please help?

Thank you

prof_mast.xlsx
	H	I	J	K	L	M	N	O	P	Q	R	S
2	T0871C M09470 ÆLY002 ÆLX000 P32200	T04030 M85003 ÆTD008 ÆYYY11 ÆKE100 F29601 ÆKI008 M09420 M30180 ÆRA010 P306X3	T04020 M85007 ÆTD014 ÆYYY11 ÆKE100 F29601 ÆKI010 M09420 M30180 M0940A P306Y3	T04030 M49000 M09450 P30624
3	T04030 M85003 P3B000 F29525 F29521 P30990 P20002	T04020 M09450 P3B000 M09011 P20002 P30990
4	T0871A M09450 P32200 P30841	T0871A M09450 P32200 P30841	T0871A M09450 P32200 P30841	T04020 M85203 F29521 F29555 F29601 M09400 P306X3 P30841	T04020 M09450 P30620 P30841	T08710 M09450 P30620 P30841	T04020 M09450 P30620 P30841
5	T04020 M85003	T04020 M09450
6	T04020 M09450 P306Y3 Æ00131	T04020 M00020 P32940
7	T04020 M09450 P30990
8	T04020 M09450 P306Y3
9	T04030 M8000A F29525 P30990 P30841	T04030 M09450 P30990 P30841
10	T0871A M80106 P32200 P30841	T04030 M18000 M85003 P30620 P30841	T08710 M09450 P30620 P30841	T04030 M09450 P30620 P30841	T04030 M85003 MÆ0020 P30620 P30841	T08710 M09450 P30620 P30841
11	T04020 M09450 P30620 P11010
12	T04020 M49000 M09450 P30990
13	T0871A M09470	T04030 M09450 P306Y3	T04020 M09450 P306Y3
14	T04030 M09450 Æ00361 P30840
15	T04020 M85003 ÆTD013 ÆYYX30 ÆKE070 F29601 ÆKI080 M09421 M09405 ÆRF005 P3B000 P306X3	T04020 M09450 M0940A
16	T04030 M09450 M54110 P30990
17	T04020 M09450 M90100 P11010
18	T04030 M09450 M49000 P30992
19	T0871C M80106 Æ00135 Æ83200 P30620	T04030 M09450 P30620	T04030 M85003 ÆYYBR3 ÆTD055 ÆKE100 F29601 ÆKI002 M09400 P306Y3 Æ83200 P32940	T04020 M09450 P306Y3 Æ00131
20	T04030 M09450 M49000 P30990 P30841
21	T0871A M09470 P32200	T0871A M09470 P32200	T04030 M18000 M09451 P306X3	T04030 M09450 P30620	T04030 M09450 P30620
22	T04020 M09450 P30624	T08711 M09470 ÆLY013 P30620
23	T04030 M09450	T04030 M85002	T04030 M85003 M85002	T04030 M85003 M85002	T04030 M85003 M85002	T08710 M09450	T08710 M80106
24	T04020 M80103 P30990	T04020 M09450 M09013 P30990
25	T04030 M80103 ÆKE050 F29555 F29601 P30990	T04030 M49000 M09450 P30990
26	T04030 M85003 ÆTD016 ÆYYY13 ÆKE055 ÆKI080 F29601 ÆRA006 P306X3 P32940	T0871C M09450	T04030 M09450 P30624
27	T04030 M49060 M09450 P30990
28	T04020 M09450 M30181 MÆ0024 P30990 P01535
29	T04030 M09450 P306X3
30	T04030 M09450 P30620
31	T04020 M80103 M85203 F29521 F29601 P30990	T04020 M09450 M00100 P30990	T04020 M80103 M85203 F29521 F29601 P30990
32	T04020 M09450 P30620 P32200	T04020 M84803 F29601 P306Y4 M09400
Profylaktisk mastektomi

prof_mast.xlsx
	AB	AC	AD
1	profmast_svardato	mattype tekst	snomed
2	23-06-2014	Hist. store
3	23-06-2014	Hist. store
4	23-06-2014	Hist. store
5	23-06-2014	Hist. store
6	15-03-2000	Hist. små
7	15-03-2000	Hist. små
8	17-03-2009	Hist. store
9	17-03-2009	Hist. store
10	17-03-2009	Hist. store
11	17-03-2009	Hist. store
12	17-03-2009	Hist. store
13	17-03-2009	Hist. store
14	17-03-2009	Hist. store
15	13-02-2004	Hist. store
16	13-02-2004	Hist. store
17	19-03-2019	Hist. store
18	19-03-2019	Hist. store
19	10-09-2003	Hist. små
20	31-01-2008	Hist. store
21	12-05-2009	Hist. små
22	12-05-2009	Hist. små
23	01-07-2009	Hist. store
24	01-07-2009	Hist. store
25	01-07-2009	Hist. store
26	01-07-2009	Hist. store
27	01-07-2009	Hist. store
28	01-07-2009	Hist. store
29	11-04-2019	Hist. store
30	16-03-2020	Hist. små
31	08-02-2011	Hist. store
32	08-02-2011	Hist. store
33	08-02-2011	Hist. store
34	18-01-2010	Hist. store
35	30-06-2015	Hist. store
36	30-06-2015	Hist. store
37	23-11-2016	Hist. små
38	10-11-2021	Hist. store
39	24-01-2017	Hist. små
40	26-07-2017	Hist. store
Profylaktisk mastektomi

 

[alex_kos92]

Welcome to the MrExcel board!

We may understand better if you give us some sample data and expected results that we can test with. The following may help:
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

Hello

I have done that, this is only a part of it for example.
As you can see, H2-K2 have values. AD2-AD5 have the same date, because they are from the same sample. I need H2 in AD2, I2 in AD3, J2 in AD4, and K2 in AD5. L2-S2 are empty, so the macro needs to look up in H3 to fill in AD6. And so on.

Thanks you

 

[alex_kos92]

Welcome to the MrExcel board!

We may understand better if you give us some sample data and expected results that we can test with. The following may help:
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

Hi again

Here is what I expect in a new sheet

prof_mast.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1	snomed								profmast_svardato	mattype tekst	snomed
2	T0871C M09470 ÆLY002 ÆLX000 P32200	T04030 M85003 ÆTD008 ÆYYY11 ÆKE100 F29601 ÆKI008 M09420 M30180 ÆRA010 P306X3	T04020 M85007 ÆTD014 ÆYYY11 ÆKE100 F29601 ÆKI010 M09420 M30180 M0940A P306Y3	T04030 M49000 M09450 P30624					23-06-2014	Hist. store	T0871C M09470 ÆLY002 ÆLX000 P32200
3	T04030 M85003 P3B000 F29525 F29521 P30990 P20002	T04020 M09450 P3B000 M09011 P20002 P30990							23-06-2014	Hist. store	T04030 M85003 ÆTD008 ÆYYY11 ÆKE100 F29601 ÆKI008 M09420 M30180 ÆRA010 P306X3
4	T0871A M09450 P32200 P30841	T0871A M09450 P32200 P30841	T0871A M09450 P32200 P30841	T04020 M85203 F29521 F29555 F29601 M09400 P306X3 P30841	T04020 M09450 P30620 P30841	T08710 M09450 P30620 P30841	T04020 M09450 P30620 P30841		23-06-2014	Hist. store	T04020 M85007 ÆTD014 ÆYYY11 ÆKE100 F29601 ÆKI010 M09420 M30180 M0940A P306Y3
5									23-06-2014	Hist. store	T04030 M49000 M09450 P30624
6									15-03-2000	Hist. små	T04030 M85003 P3B000 F29525 F29521 P30990 P20002
7									15-03-2000	Hist. små	T04020 M09450 P3B000 M09011 P20002 P30990
8									17-03-2009	Hist. store	T0871A M09450 P32200 P30841
9									17-03-2009	Hist. store	T0871A M09450 P32200 P30841
10									17-03-2009	Hist. store	T0871A M09450 P32200 P30841
11									17-03-2009	Hist. store	T04020 M85203 F29521 F29555 F29601 M09400 P306X3 P30841
12									17-03-2009	Hist. store	T04020 M09450 P30620 P30841
13									17-03-2009	Hist. store	T08710 M09450 P30620 P30841
14									17-03-2009	Hist. store	T04020 M09450 P30620 P30841
Ark1

 

[alex_kos92]

Other solution found

 

[Peter_SSs]

Good to hear you got the solution, and thanks for letting us know.

If you would like to post the solution then it is perfectly fine to mark that post as the solution to help future readers. Otherwise, please do not mark a post that doesn't contain a solution.