Match cell in column then find first blank cell in that row and record data

tanke

New Member
Joined
Aug 30, 2016
Messages
8
Office Version
  1. 365
Platform
  1. Windows
Hi team, I've been having a great deal of trouble trying to put together what should be a relatively simple worksheet!

I have managed to work out most of the individual steps but things break down when I try to combine them.

My goal is to enter a name followed by 2 dates (initial and end) (this I can do!)

Then the worksheet should look for the name (column B) and record all dates between the initial and end date in the first empty cell alongside that name.

1660702349577.png


I've tried all sorts of combinations starting with Index and Match but can't figure out how to first identify the correct row (based on name) then the first empty cell in that row, then enter the appropriate dates from that cell onwards.

Names will typically be added to the list over time. Dates will be added over time and max out at 60 entries.

If someone can help me out with some VBA magic it will be much appreciated!
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

This would also help in the future:
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.


This could be done with formulas so does it have to be vba?
 
Upvote 0
thanks for your reply Peter, I have updated my account details and a mini sheet is included below

I'm happy with either formulas or VBA, just can't seem to figure out how to find a cell (name) in a column then find the first blank cell in that row then copy dates from initial date to end date from that first blank cell onwards.

I think the bit I'm missing is using the cell identified when searching for a name as the identifier for the row to be searched for the fist blank cell.

Your help is much appreciated.

name and date records.xlsx
ABCDEFGHIJKLMNO
1
2namedates
3john1/05/2022
4joe3/05/20224/05/20225/05/2022
5frank3/05/2022
6sally4/04/20221/05/20223/05/2022
7mary
8tom
9
10
11
12Enter nameDate intialDate end
13frank7/05/202212/05/2022
14
15
16
17
185searches for 'frank' and returns row number
194searches for first blank cell in row and returns column number, not sure how to link this to above row number (result for 'frank')
20$D$5returns cell address for intersection of 'frank' (row number) and first blank cell (column number)
21
22
Sheet1
Cell Formulas
RangeFormula
F18F18=MATCH(H13,B:B,0)
F19F19=MATCH(9E+99,5:5)+1
F20F20=CELL("address",INDEX(A1:H8,F18,F19))
 
Upvote 0
Thanks for updating your details and for the XL2BB data. (y)
I don't understand the values in I13:J13 in the post 3 Mini Sheet as I thought this is what you were after.

22 08 20.xlsm
ABCDEFGHIJ
1
2namedates
3john1/05/2022
4joe3/05/20224/05/20225/05/2022
5frank3/05/2022
6sally4/04/20221/05/20223/05/2022
7mary
8tom
9
10
11
12NameDate initialDate end
13sally4/04/20223/05/2022
Start End Dates
Cell Formulas
RangeFormula
I13I13=LET(m,MIN(FILTER(C3:BZ8,B3:B8=H13,0)),IF(m=0,"",m))
J13J13=IF(I13="","",MAX(FILTER(C3:BZ8,B3:B8=H13)))
 
Upvote 0

Forum statistics

Threads
1,213,568
Messages
6,114,348
Members
448,570
Latest member
rik81h

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

Which adblocker are you using?

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
Back
Top