aligning data in excel

Hi Rohn

Just in case Power Query M reference is here

#1 Create a connection on Table1

#2 Create a connection on Table2

#3 Create a new query and load it as a connection:

a) Take column [code] from Table1 and column [code] from Table2

b) Combine (append) the above 2 Tables

>> #3a and #3b are a single step: Source

c) Remove duplicates (Table.Distinct)

d) Sort the remaining codes

e) Add an Index column (here from 0 but can start with 1, 2, 10...)

h) Rename column [code] i.e. [UniqCode]

That query will allow sorting the records/rows in the final (#4 query)

#4 (the one I called Table1Table2_Aligned):

a) Join Table1 and Table2 on their respective [code] column:

(each record in column [Table2] contains the record(s) in Table2 that match in Table1. In this context each Table consists of a single record)

b) Table.ColumnNames(Source[Table2]{0}) >> Get the column names of the 1st Table in column [Table2] (Tables and Lists indexes start at 0, hence {0} above). In this context we get a list of 3 column names: {"code","name","value july"}>> You know a Table cannot have 2 columns with the same names but this is what the OP expects with columns [code] and [name]

c) List.Transform(ColumnsToExpand, each _ &" ") >> Takes the list from #4b ({"code","name","value july"}) and for each item, add a <space> at the end

>> I don't like that but only way AFAIK to get exactly what the OP expects

d) Expand columns **{"code","name","value july"}**of nested tables in [Table2] and respectively give them names {"code ","name ","value july "} (list from #4c)

e) Add a temp. column [CodeToJoin] that - with an if...then… else - takes the value from column [code] if it's not null otherwise from column [code ]

>> With this we have no more holes (null value) in codes

f) Join current Table on column [CodeToJoin] with Table from #3, column [UniqCode]

g) Sort column [Idx0]

h) Remove columns [CodeToJoin],[UniqCode] and [Idx0]

(won't do that every day)

Hi

You can try this as shown in picture1 - use Vlookup.

and as shown in next picture, copy all the cells and paste special - value,

create a added codes in I2= A2+E2 and copy all the way down for the same column

then apply sort based on I2

remove the 0 values rows, as shown in picture 2

HI V. the codes are real codes, i have 400 customers with store codes ranging from hen001 to hen999, only active codes will show in the spreadsheet. i also have 200 customers with codes has001 to has999 in  different sheet

I don't think you can do exactly what you want, but try these articles

lists- Compare two columns and list differences in Excel?

https://www.extendoffice.com/documents/excel/3041-excel-compare-two-columns-and-list-differences.html

Let’s say, you have two lists of student’s names in Column A and Column B of your worksheet, and some of them are both in the two columns, but now, you just want to compare these two columns and list the different names from them.

lists- compare two columns for (highlighting) missing values in Excel?

For example I have two fruit lists in two worksheets as below screenshots shown, and now I need to compare these two lists and find out missing values in each list. It may be not easy to solve it manually. In this article, I will introduce two tricky ways to solve it easily.

.  *  Compare two columns for (highlighting) missing values with formulas

.  *  Compare two columns for (highlighting) missing values with Kutools for Exc

Hello

I am V. Arya, Independent Advisor, to work with you on this issue. It would require macro to do this.

But to catch the right logic for alignment, I need to know whether codes are real codes or dummy codes.

For example if they are real codes I need to use number in hen say 003 to sort data otherwise I will need to use some logic to sort data.