Необходимо добавить пропущенные номера идентификаторов итераций в серию в текстовом формате или в Excel. Как это сделать без кодирования?

Question

У меня есть текстовый, CSV или Excel файл, который выглядит как

||--ID-----||--Name--||--Date of birth--||
    1            Jo          1/1/11
    32           Mo          2/2/12
    3382         Ro          3/3/10
    21,252       Do          4/4/09

Реальный набор содержит 280 000 из них из 1 000 000 строк. Мне нужно как-то добавить все пропущенные идентификационные номера (которые являются последовательными +1 итерациями) и просто пустые поля для имени и доб. Так что я получаю что-то похожее на:

||--ID-----||--Name--||--Date of birth--||
    1,           "Jo",     "1/1/11"
    2,           "",         ""
    3,           "",         ""
    4,           "",         ""

вплоть до 32, затем снова, пока каждое целое число не будет там. Есть простой способ сделать это? без записи блока кода в цикле? Все что угодно из Excel, какого-либо приложения или любого трюка с текстовым редактором Windows будет приветствоваться.

Изменить: Пожалуйста, игнорируйте запятые, кавычки и т.д .. Недостающие идентификационные номера - единственное, что критично.

Answer 1

Одним из подходов будет:

1. импортируйте свой CSV, чтобы преуспеть как лист 1.
2. на листе 2 создайте свой полный список идентификационных номеров по формуле, такой как a2: = a1+1, затем скопируйте вниз.
3. используйте формулы vlookup в двух других столбцах, ссылаясь на таблицу на листе 1. например, b2: =VLOOKUP(A2,Sheet1!A2:C13,2,false) и c2: =VLOOKUP(A2,Sheet1!A2:C13,3,false) , или для точного соответствия вашему запросу позволяет обернуть его в оператор IFNA, чтобы он возвращал "", если значение отсутствует. b2: =IFNA(VLOOKUP(A2,Sheet1!A2:C13,2,FALSE),"") и c2: =IFNA(VLOOKUP(A2,Sheet1!A2:C13,3,FALSE),"") (теперь эти формулы можно скопировать в столбец.)
4. После заполнения сохраните лист 2 как CSV.

Примечание: для правильной работы таблицы на листе 1 необходимо будет отсортировать по столбцу 1. Судя по предоставленной информации, это выглядит так, но если это не так, сортируйте информацию по первому столбцу.

С миллионами строк это будет очень медленным на многих компьютерах, я пропустил это, прежде чем писать формулы. Однажды я сделал нечто подобное с большим набором данных, отключив auto relcalc, чтобы получить все формулы на месте, затем выполнил ручной пересчет. Это заняло несколько часов, но завершилось правильно.

Answer 2

Я бы разрешил это с помощью Power Query Add-In.

Я создал прототип, который вы можете просмотреть или скачать - его демонстрация Power Query - добавление недостающих идентификационных номеров в series.xlsx »на моем One Drive:

https://onedrive.live.com/redir?resid=4FA287BBC10EC562%21398

Для этого требуется несколько шагов и немного кода на языке Power Query (M) для вызова списка.Функция Numbers (она не отображается в пользовательском интерфейсе Power Query). Тем не менее, это всего лишь одна простая строка кода - остальная ее часть может быть построена путем щелчка мышью в Power Query.

В основном моя техника заключалась в использовании списка.Числа для генерации таблицы идентификационных номеров, затем я добавил Merge, чтобы получить столбцы из входных данных (где существует идентификационный номер).

Документация для Списка.Номера здесь:

http://office.microsoft.com/en-au/excel-help/list-numbers-HA104111648.aspx?CTT=5&origin=HA104122363

Огромный "шляпный совет" Мэтту Массону за технику "Настройки".

http://www.mattmasson.com/2014/04/defining-configurable-settings-for-your-queries/

Примечание Power Query может читать напрямую из файла CSV, который я бы использовал в качестве источника для запроса "Входные данные". Вероятно, проще всего удалить этот запрос, затем создать новый из файла CSV и назвать его "Входные данные". Снимите флажок Загрузить на лист, чтобы сохранить ресурсы.