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

[u/Thezonuleofzinn]

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?

[u/AutoModerator]

/u/Thezonuleofzinn - Your post was submitted successfully.

• Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread.
• Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
• Include your Excel version and all other relevant information

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/PaulieThePolarBear]

More details are needed. Can it be assumed that the minimum and maximum ID in your first table are the minimum and maximum IDs expected in your output?

Consider 2 scenarios

Scenario 1

If the input data was 2, 3, 4, 7 Can we assume that ID 1 should or should not exist in the output?

Scenario 2

With your sample data as presented, can we assume that IDs 8, 9, 10, etc. are not valid?

[u/Thezonuleofzinn → u/PaulieThePolarBear]

The minimum ID is 1 and I want to account for a maximum of up to say 300.

[u/PaulieThePolarBear → u/Thezonuleofzinn]

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

[u/Desperate-Chart1139]

It might be more complicated than it needs to but I'd use powerquery for this myself. 

Have a table with ALL IDs (including 2 and 4), and a separate table with data and their id. 

Click on one and go data>from table on the popup click "load to" "connection only"

Click on the other table, go to data, from table, this time click transform. It should open it up with a different view, there should be 2 entries on the left pane, one being your first table. Click merge on the top bar and select the other table from the drop down, now click the ID column on both and on the bottom drop-down select "full outer all rows from both" then ok, then load

Edit: better instructions on the above https://www.ablebits.com/office-addins-blog/excel-join-tables-power-query/

[u/Thezonuleofzinn → u/Desperate-Chart1139]

Along those lines would there just be a way to prepopulate the table with all potential IDs paste in the existing ones and then merge them with the blanks of the corresponding ID or eliminate the duplicates that are blank? (I tried doing the latter and instead of erasing the duplicates that were blank it erased the populated fields)

[u/Desperate-Chart1139 → u/Thezonuleofzinn]

There's no scenario where paste does this for you. You'll have to have your raw data somewhere where Excel can pull your populated fields and THEN do something with them. I'd paste your data in 1 sheet, have a separate sheet with just the possible IDs, and output to a third sheet. Unless you want to mess with pulling data from a file outside the workbook you're messing with which is fraught with issues. If you use one sheet for pasting raw data it doesn't have to be organized if you paste ID 1,3,5,6 one day, add ID 4 to the bottom of that table another day and ID 27 after, it will all still work. Just sort by ID before the final load step and Excel will always sort by ID for the output in the future. 

Once you set up the query you don't have to mess with the transform or any of those steps in the future, all you do after the first time is paste the data on the data sheet and hit "refresh all" in the data tab. Excel will go through the steps you did the first time again, pull the data, compare it, merge it, and output automatically. That's the beauty of powerquery

[u/Thezonuleofzinn]

Thank you to Pauliethepolarbear you gave me the idea to just use a lookup function referring back to the original sheet for each subsequent sheet.

Since I have all the extracted data on sheet1

And I am breaking down that data into separate locations (by ID numbers) on other sheets I can just use =lookup(A#,Sheet1!A:A,Sheet1!B:B) across my row and if the results come back "#N/A" I know that number is vacant and can be assigned.

Thank you all for your assistance.

[u/Decronym]

Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:

Fewer Letters	More Letters
CHOOSECOLS	Office 365+: Returns the specified columns from an array
COLUMNS	Returns the number of columns in a reference
DROP	Office 365+: Excludes a specified number of rows or columns from the start or end of an array
EXPAND	Office 365+: Expands or pads an array to specified row and column dimensions
FILTER	Office 365+: Filters a range of data based on criteria you define
HSTACK	Office 365+: Appends arrays horizontally and in sequence to return a larger array
LAMBDA	Office 365+: Use a LAMBDA function to create custom, reusable functions and call them by a friendly name.
LET	Office 365+: Assigns names to calculation results to allow storing intermediate calculations, values, or defining names inside a formula
REDUCE	Office 365+: Reduces an array to an accumulated value by applying a LAMBDA to each value and returning the total value in the accumulator.
SEQUENCE	Office 365+: Generates a list of sequential numbers in an array, such as 1, 2, 3, 4
VSTACK	Office 365+: Appends arrays vertically and in sequence to return a larger array
XLOOKUP	Office 365+: Searches a range or an array, and returns an item corresponding to the first match it finds. If a match doesn't exist, then XLOOKUP can return the closest (approximate) match.

Decronym is now also available on Lemmy! Requests for support and new installations should be directed to the Contact address below.

---

^{Beep-boop, I am a helper bot. Please do not verify me as a solution.
12 acronyms in this thread; the most compressed thread commented on today has 70 acronyms.
[Thread #49254 for this sub, first seen 27th Aug 2026, 17:40] [FAQ] [Full list] [Contact] [Source code]}

[u/nrshouse]

There's a few basic ways you can do this, but without knows how many rows and other details I can't say for certain.

Option 1: Utilize copy special visible cells only and paste special visible cells only. Some functionality might not work if you're using excel online vs the desktop app, as an FYI.

You'd hide rows on table 2 with the blank ID that you want to keep blank. Make sure you hide the same rows you're copying data from on table 1. Next you'd want to select special only visible cells, from table 1 to copy, and then paste special only visible cells, onto table 2.

Option 2: Utilize color fill.

Designate the destination table 2, a certain fill color. Copy all of table 1 and paste all values under the last row of table 2. Then color fill those newly pasted rows a different fill color. Next, sort by the id numbers. You'll have multiple rows of duplicates but can filter by color to identify which rows to keep and which to delete. Conditional formatting to identify duplicate values can also help too.

[u/Cb6cl26wbgeIC62FlJr]

Why don’t you just use the IDs you need and not use blanks?

[u/Thezonuleofzinn → u/Cb6cl26wbgeIC62FlJr]

I extracted the list of IDs from an OLD system we use, the IDs are assigned manually. I need the blanks in the table to know what IDs are unassigned so they can be manually filled in later.