Source
r/excel•
ProfShea

How can I reorder cells automatically?

unsolved

My workflow is insane and probably not best solved with excel. But, this is the software we use involving hundreds of people throughout the world. To begin:

  1. We receive customer requests. Data input team puts relevant data in cells across columns. Each customer has 10 different cells for data like: First name, last name, email, etc. There are anywhere between 30 and 100 requests a day.

  2. We submit that data by copying and pasting chunks of it to an internationally managed/utilized excel spreadsheet. The data on this sheet is mostly the same YET it is in a different order. I want to copy/paste the 30 to 100 rows of customer data to another page and get the data ordered as it is supposed to be on that internationally managed excel spreadsheet.

For example. Our data is stored First name; last name; country; email. The second excel sheet requires last name; first name; email; country. How can I do this?

Posted Feb 28, 2021, 7:22 PM UTC

17
AutoModeratorMOD•

u/ProfShea - Your post was submitted successfully.

Please read these reminders and edit to fix your post where necessary:

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.

1
Veryspecialthermos4u10•

If you must use Excel...

  1. Put data data entry spreadsheet.
  2. Then create a power query spreadsheet that queries the data spreadsheet and re-arranges the column order.

Alternatively create a pivot table on a new tab in the same workbook that pulls from your data entry tab. Add the columns you need for the international spreadsheet to the 'row section' of the pivot table. (Format settings: format as table, repeat labels) Refresh the pivot table and filter as needed then paste the pivot table rows into your international spreadsheet.

1
ProfSheaOP•

what is a power query spreadsheet?

1
Veryspecialthermos4u10•

https://support.microsoft.com/en-us/office/import-data-from-external-data-sources-power-query-be4330b3-5356-486c-a168-b68e9e616f5a

1
YaksLikeJazz•

+1 for the specialthermos' idea

https://docs.microsoft.com/en-us/powerquery-m/table-reordercolumns

1
Way2trivial84•

I can give you actual formulas that should work,
confirm your data matches the below

lacking sample data- I can 'stab at it' with a few answers

+ABCD
1first namelast namecountryemail

^(Table) ^(formatting) ^(brought) ^(to) ^(you) ^(by) ^(ExcelToReddit)

second!

+ABCD
1last namefirst nameemailcountry

^(Table) ^(formatting) ^(brought) ^(to) ^(you) ^(by) ^(ExcelToReddit)

​

hypothetical data falls between rows 2-20 on both

first!

add item e2 and copy down to e20

=VALUE(COUNTIF(B$2:B$20,"<="&B2)&TEXT(ROW(),"000"))

item a2 on second=indirect("first!b"&right(small(first!e$2:e$20,row()-1),3))

second!
item b2

=indirect("first!a"&right(small(first!e$2:e$20,row()-1),3))

item c2 on second

=indirect("first!d"&right(small(first!e$2:e$20,row()-1),3))

item d2 on second

=indirect("first!c"&right(small(first!e$2:e$20,row()-1),3))

1
Decronym•

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

|Fewer Letters|More Letters| |-------|---------|---| |COUNTIF|Counts the number of cells within a range that meet the given criteria| |ROW|Returns the row number of a reference| |TEXT|Formats a number and converts it to text| |VALUE|Converts a text argument to a number|

^(Beep-boop, I am a helper bot. Please do not verify me as a solution.)
^(4 acronyms in this thread; )^(the most compressed thread commented on today)^( has 9 acronyms.)
^([Thread #4471 for this sub, first seen 1st Mar 2021, 02:40]) ^[FAQ] ^([Full list]) ^[Contact] ^([Source code])

1