Happy New Year
Hi,
I have the attached sample table, please help me to fill the employee Name and ID by using a formula as shown yellow highlighted.
Regards,
VKKT
Fill-in data using formula
-
- 2StarLounger
- Posts: 184
- Joined: 13 Jun 2018, 07:50
Fill-in data using formula
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78586
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Fill-in data using formula
The problem is that A3, B3 etc. look empty but they aren't. We're going to fix that first.
Select A1:B17.
Press Ctrl+H to activate the Replace dialog.
Leave the 'Find what' box empty, and enter a space in the 'Replace with' dialog.
If you don't see the search options, click 'Options >>'.
Tick the check box 'Match entire cell contents'.
Click 'Replace All'.
Next, enter a space in the 'Find what' box and clear the 'Replace with' box.
Click 'Replace All'.
Close the Replace dialog.
Press F5 or Ctrl+G to activate the Go To dialog.
Click Special...
Select Blanks, then click OK.
The empty-looking cells should now be selected, with A3 as the active cell in the selection.
Enter the formula =A2 and confirm it by pressing Ctrl+Enter.
Select A1:B17.
Press Ctrl+H to activate the Replace dialog.
Leave the 'Find what' box empty, and enter a space in the 'Replace with' dialog.
If you don't see the search options, click 'Options >>'.
Tick the check box 'Match entire cell contents'.
Click 'Replace All'.
Next, enter a space in the 'Find what' box and clear the 'Replace with' box.
Click 'Replace All'.
Close the Replace dialog.
Press F5 or Ctrl+G to activate the Go To dialog.
Click Special...
Select Blanks, then click OK.
The empty-looking cells should now be selected, with A3 as the active cell in the selection.
Enter the formula =A2 and confirm it by pressing Ctrl+Enter.
Best wishes,
Hans
Hans
-
- 4StarLounger
- Posts: 587
- Joined: 14 Nov 2012, 16:06
Re: Fill-in data using formula
In VBA:
or
Code: Select all
Sub M_snb()
For Each it In Cells(1).CurrentRegion.Columns(1).Cells
If Len(it) < 2 Then it.Resize(, 2).Value = it.Resize(, 2).Offset(-1).Value
Next
End Sub
Code: Select all
Sub M_snb()
sn = Sheet1.Cells(1).CurrentRegion.Columns(1).Resize(, 2)
For j = 3 To ubound(sn)
If Len(sn(j, 1)) < 2 Then sn(j, 1) = sn(j - 1, 1)
If Len(sn(j, 2)) < 2 Then sn(j, 2) = sn(j - 1, 2)
Next
Sheet1.Cells(1).CurrentRegion.Columns(1).Resize(, 2) = sn
End Sub
-
- 2StarLounger
- Posts: 184
- Joined: 13 Jun 2018, 07:50
Re: Fill-in data using formula
Greetings!
It is working well, sorry for the delay in response, I was busy with something else now only I got time to do this.
Thanks Hans for your support.
Thanks snb
Regards,
VKKT
It is working well, sorry for the delay in response, I was busy with something else now only I got time to do this.
Thanks Hans for your support.
Thanks snb
Regards,
VKKT