Formula Help - Duplication

jamescooper

Well-known Member
Hello, If everytime there is a blank in column B, I want to produce 2 entries for the column A, how would I do this in a formula please?

Thanks.

 CARMARTHN O CARMARTJN P FERRYSIDE P KIDWELLY P PEMBREYBP LLANELLI LLANDILOJ P

<colgroup><col><col span="2"></colgroup><tbody>
</tbody>

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
So to clarify things what would your output look like?

 CARMARTHN CARMARTJN FERRYSIDE KIDWELLY PEMBREYBP PEMBREYBP LLANELLI LLANELLI LLANDILOJ

<colgroup><col width="64" style="width:48pt"> </colgroup><tbody>
</tbody>

Any ideas here please?

Many thanks.

Any ideas? Thanks

No ideas ?

I got this, just done something very similare somewhere else, give me a few mins...

in the same Sheet

insert a column so the CARMARTHN column is column B, and the Os and Ps are column C

in D1 put
=IF(C1<>"",1,2)
and copy down to D7

in A1 put 1
in A2 put =A1+D1
and copy down to A7

in E1
=IF(ROW()>SUM(D\$1:D\$7),"",ROW())
and copy down, make sure you drag it enough to cover the total entries in this case 9

in column F
=IFERROR(VLOOKUP(E1,\$A\$1:\$B\$7,2),"")
and copy down, make sure you drag it enough to cover the total entries in this case 9

You should now have a list of the towns as you want.

Replies
5
Views
161
Replies
6
Views
302
Replies
6
Views
283
Replies
2
Views
137
Replies
3
Views
107

Forum statistics

Threads
1,203,491
Messages
6,055,727
Members
444,814
Latest member
AutomateDifficulty

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back