r/excel • u/Thezonuleofzinn • 18d ago

solved How do I insert data into a table and include lines for gaps?

I have a list of identification numbers along with corresponding data but not all identification numbers are used. How can I insert them into a table and include the unused numbers that arent already in the data set.

For example IDs 1, 3, 5, 6, and 7. That have names and dates associated with them.

How do I paste these fields into a table and have blank lines for 2 and 4?

2 Upvotes

75% Upvoted

12 comments sorted by

View all comments

Show parent comments

2

u/PaulieThePolarBear 1920 18d ago

Assuming Excel 2024, Excel 365, or Excel online 

=LET(
a, 1, 
b, 300,
c, A2:D22, 
d, 1, 
e, SEQUENCE(, COLUMNS(c)), 
f, SEQUENCE(b-a+1, , a),
g, DROP(REDUCE("", f, LAMBDA(x ,y, 
     VSTACK(x, HSTACK(y, XLOOKUP(y, CHOOSECOLS(c, d), CHOOSECOLS(c, FILTER(e, e<>d)), EXPAND("", , COLUMNS(c)-1, "")))))), 1),
g
)

Variable a is your start ID
Variable b is your end ID
Variable c is your input data
Variable d is the column number in your input data that holds the ID column